Counting the number of rows in a table
Posted on May 18, 2015
| 5 minutes
| spaghettidba
Don’t be fooled by the title of this post: while counting the number of rows in a table is a trivial task for you, it is not trivial at all for SQL Server.
Every time you run your COUNT(*) query, SQL Server has to scan an index or a heap to calculate that seemingly innocuous number and send it to your application. This means a lot of unnecessary reads and unnecessary blocking.
[Read More]How to post a T-SQL question on a public forum
Posted on April 24, 2015
| 5 minutes
| spaghettidba
If you want to have faster turnaround on your forum questions, you will need to provide enough information to the forum users in order to answer your question.
In particular, talking about T-SQL questions, there are three things that your question must include:
Table scripts Sample data Expected output Table Script and Sample data Please make sure that anyone trying to answer your question can quickly work on the same data set you’re working on, or, at least the problematic part of it.
[Read More]Tracking Table Usage and Identifying Unused Objects
Posted on April 20, 2015
| 17 minutes
| spaghettidba
One of the things I hate the most about “old” databases is the fact that unused tables are kept forever, because nobody knows whether they’re used or not. Sometimes it’s really hard to tell. Some databases are accessed by a huge number of applications, reports, ETL tools and God knows what else. In these cases, deciding whether you should drop a table or not is a tough call.
Search your codebase The easiest way to know if a table is used, is to search the codebase for occurences of the table name.
[Read More]Should I check tempdb for corruption?
Posted on March 2, 2015
| 3 minutes
| spaghettidba
You all know that checking our databases for corruption regularly is a must. But what about tempdb? Do you need to check it as well?
The maintenance plans that come with SQL Server do not run DBCC CHECKDB on tempdb, which is a strong indicator that it’s a special database and something different is happening behind the scenes. If you think that relying on the behavior of a poor tool such as maintenance plans to make assumptions on internals is a bit far-fetched, well, I see your point.
[Read More]Speaking at SQLSaturday Pordenone
Posted on February 20, 2015
| 2 minutes
| spaghettidba
Next week, on Saturday 28, make sure you don’t miss SQLSaturday Pordenone!
Pordenone is the place where the Italian adventure with SQLSaturday started, more than two years ago. It was the beginning of a journey that brought many SQLSaturdays to Italy, with our most successful one in Parma last November.
Now we’re back in Pordenone to top that result!
We have a fantastic schedule for this event, with a great speaker lineup and great topics for the sessions.
[Read More]Please Throw this Hardware at the Problem
Posted on February 12, 2015
| 5 minutes
| spaghettidba
We’re being told over and over that “throwing hardware at the problem” is not the correct solution for performance problems and a 2x faster server will not make our application twice as fast. Quite true, but there’s one thing that we can do very easily without emptying the piggy bank and won’t hurt for sure: buying more RAM.
The price for server-class RAM has dropped so dramatically that today you can buy a 16 GB module for around € 200.
[Read More]Blame it on Connect
Posted on February 2, 2015
| 13 minutes
| spaghettidba
Some weeks ago I blogged about the discouraging signals coming from Connect and my post started a discussion that didn’t go very far. Instead it died quite soon: somebody commented the post and ranted about his Connect experience. I’m blogging again about Connect, but I don’t want to start a personal war against Microsoft: today I want to look at what happened from a new perspective.
What I find disappointing is a different aspect of the reactions from the SQL Server community, which made me think that maybe it’s not only Connect’s fault.
[Read More]Installing multiple default instances on a single server
Posted on January 29, 2015
| 8 minutes
| spaghettidba
As you probably know, SQL Server allows only one default instance per server. The reason is not actually something special to SQL Server, but it has to do with the way TCP/IP endpoints work.
In fact, a SQL Server default instance is nothing special compared to a named instance: it has a specific instance id (MSSQLSERVER) and listens on a well-known TCP port (1433), but it has no other intrinsic property or feature that makes it different from any other instance.
[Read More]The big disconnect with Connect
Posted on January 7, 2015
| 5 minutes
| spaghettidba
A couple of years ago I blogged about a bug on the Data Collector that I couldn’t resolve but with an ugly workaround. At the end of that post, I stated that I wouldn’t have bothered filing the bug on Connect, due to prior discouraging results. Well, despite what I wrote there, I took the time to open a bug on Connect (the item can be found here), which was promptly closed as “won’t fix”.
[Read More]I'm an MVP: now what?
Posted on January 2, 2015
| 3 minutes
| spaghettidba
Today when I checked my mailbox I found an amazing surprise: I joined the ranks of the Most Valuable Professionals for SQL Server!
I am honoured to join a community of people that I highly respect and have always been my inspiration. The MVPs I had the pleasure to meet are a model to strive for: exceptional technical experts and great community leaders that devote their own time to spread their knowledge.
[Read More]