dba_runCHECKDB v2(012)
Posted on February 27, 2013
| 3 minutes
| spaghettidba
If you are one among the many that downloaded my consistency check stored procedure called “dba_RunCHECKDB”, you may have noticed a “small” glitch… it doesn’t work on SQL Server 2012!
This is due to the resultset definition of DBCC CHECKDB, which has changed again in SQL Server 2012. Trying to pipe the results of that command in the table definition for SQL Server 2008 produces a column mismatch and it obviously fails.
[Read More]Discovering resultset definition of DBCC commands in SQL Server 2012
Posted on February 26, 2013
| 2 minutes
| spaghettidba
Back in 2011 I showed a method to discover the resultset definition of DBCC undocumented commands.
At the time, SQL Server 2012 had not been released yet and nothing suggested that the linked server trick could stop working on the new major version. Surprisingly enough it did.
If you try to run the same code showed in that old post on a 2012 instance, you will get a quite explicit error message:
[Read More]Using QUERYTRACEON in plan guides
Posted on February 8, 2013
| 4 minutes
| spaghettidba
Yesterday the CSS team made the QUERYTRACEON hint publicly documented.
This means that now it’s officially supported and you can use it in production code.
After reading the post on the CSS blog, I started to wonder whether there is some actual use in production for this query hint, given that it requires the same privileges as DBCC TRACEON, which means you have to be a member of the sysadmin role.
[Read More]Replaying Workloads with Distributed Replay
Posted on November 22, 2012
| 6 minutes
| spaghettidba
A couple of weeks ago I posted a method to convert trace files from the SQL Server 2012 format to the SQL Server 2008 format.
The trick works quite well and the trace file can be opened with Profiler or with ReadTrace from RML Utilities. What doesn’t seem to work just as well is the trace replay with Ostress (another great tool bundled in the RML Utilities).
For some reason, OStress refuses to replay the whole trace file and starts throwing lots of errors.
[Read More]Convert a Trace File from SQLServer 2012 to SQLServer 2008R2
Posted on November 7, 2012
| 4 minutes
| spaghettidba
Recently I started using RML utilities quite a lot.
ReadTrace and Ostress are awesome tools for benchmarking and baselining and many of the features found there have not been fully implemented in SQLServer 2012, though Distributed Replay was a nice addition.
However, as you may have noticed, ReadTrace is just unable to read trace files from SQLServer 2012, so you may get stuck with a trace file you wont’ abe able to process.
[Read More]Replay a T-SQL batch against all databases
Posted on June 27, 2012
| 10 minutes
| spaghettidba
It’s been quite a lot since I last posted on this blog and I apologize with my readers, both of them :-).
Today I would like to share with you a handy script I coded recently during a SQL Server health check. One of the tools I find immensely valuable for conducting a SQL Server assessment is Glenn Berry’s SQL Server Diagnostic Information Queries. The script contains several queries that can help you collect and analyze a whole lot of information about a SQL Server instance and I use it quite a lot.
[Read More]SQL Server and Custom Date Formats
Posted on March 23, 2012
| 1 minutes
| spaghettidba
Today SQL Server Central is featuring my article Dealing with custom date formats in T-SQL.
There’s a lot of code on that page and I thought that making it available for download would make it easier to play with.
You can download the code from this page or from the Code Repository.
CustomDateFormat.cs formatDate_Islands_iTVF.sql formatDate_Recursive_iTVF.sql formatDate_scalarUDF.sql parseDate_Islands_iTVF.sql I was also asked to include a performance chart for the different methods included in the article.
[Read More]How to Eat a SQL Elephant in 10 Bites
Posted on March 15, 2012
| 13 minutes
| spaghettidba
One byte at a time, obviously!
No elephants were harmed during photoshopping. Sometimes, when you have to optimize a poor performing query, you may find yourself staring at a huge statement, wondering where to start. Some developers think that a single elephant statement is better than multiple small statements, but this is not always the case.
Let’s try to look from the perspective of software quality:
Efficiency The optimizer will likely come up with a suboptimal plan, giving up early on optimizations and transformations.
[Read More]Discovering resultset definition of DBCC commands
Posted on November 16, 2011
| 6 minutes
| spaghettidba
Lots of blog posts and discussion threads suggest piping the output of DBCC commands to a table for further processing. That’s a great idea, but, unfortunately, an irritatingly high number of those posts contains an inaccurate table definition for the command output.
The reason behind this widespread inaccuracy is twofold.
On one hand the output of many DBCC commands changed over time and versions of SQL Server, and a table that was the perfect fit for the command in SQL Server 2000 is not perfect any more.
[Read More]Concatenating multiple columns across rows
Posted on October 13, 2011
| 5 minutes
| spaghettidba
Today I ran into an interesting question on the forums at SQLServerCentral and I decided to share the solution I provided, because it was fun to code and, hopefully, useful for some of you.
Many experienced T-SQL coders make use of FOR XML PATH(‘’) to build concatenated strings from multiple rows. It’s a nice technique and pretty simple to use. For instance, if you want to create a list of databases in a single concatenated string, you can run this statement:
[Read More]