Sometimes sql server surprises me, I have a really stupid table with seven columns and one of these columns contains great amount of text data. I need to select the minimum Id based on a date filter, and so I issued a really simple query like: Select min(TN.Id) from TableName TN where TN.timestamp >= ‘20110201’ […]

Continue reading about Force Sql server index usage in query

If you issue a DBCC CHECKDB on a big database to verify for consistency errors, it will take a long time to complete, but the Management Studio windows usually does not give you any hint about how long does it take, or a percentage progress. Luckily enough sql server has a Dynamic Management View that […]

Continue reading about Check progress of DBCC CHECKDB

I’ve a stored procedure with a query that runs on a quite big database, it was slow (more than one minute to run) and was optimized using a temp table. The result is that execution time dropped to ~2 secs, and since this was acceptable the optimization stopped. After a couple of months, the query […]

Continue reading about When it is time to tweak SQL Server queries

In a production Sql Server database we had some issue with the hardware, the result is that one very big database started to gave us errors on DBCC CHECKDB, the error is the following one. Msg 8929, Level 16, State 1, Line 1 Object ID xxxxxx, index ID 1, partition ID xxxxxx, alloc unit ID […]

Continue reading about Hardcore fix error in Sql Server database

Suppose you have a big table with about 3 GB of data in and you need to add a nullable new column on it, you can open SSMS, open the table in designer and create a new column of type Int called sclo_durationInMinutes, press save and the table gets update quite immediately. Then you realize […]

Continue reading about Why Management Studio forces you to know SQL