Trace

If you ever tried to capture a benchmark on your SQL Server, you probably know that it is a complex operation. Not an impossible task, but definitely something that needs to be planned, timed and studied very thoroughly. The main idea is that you capture a workload from production, you extract some performance information, then you replay the same workload to one or more environments that you want to put to test, while capturing the same performance information. [Read More]

Oooooops! I exchanged some emails about my post with Jonathan Kehayias and looks like I was wrong on many of the points I made. I don’t want to keep misleading information around and I definitely need to fix my wrong assumptions. Unfortunately, I don’t have the time to correct it immediately and I’m afraid it will have to remain like this for a while. Sorry for the inconvenience, I promise I will try to fix it in the next few days. [Read More]

Now that my SQL Server 2014 CTP1 virtual machine is ready, I started to play with it and some new features and differences with the previous versions are starting to appear. What I want to write about today is a T-SQL enhancement to DDL statements that brings in some new interesting considerations. SQL Server 2014 now supports a new T-SQL syntax that allows defining an index in the CREATE TABLE statement without having to issue separate CREATE INDEX statements. [Read More]

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]

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]

Yesterday I posted a method to convert trace files from SQL Server 2012 to SQL Server 2008R2 using a trace table. As already mentioned in that post, having to load the whole file into a trace table has many shortcomings: The trace file can be huge and loading it into a trace table could take forever The trace data will consume even more space when loaded into a SQL Server table The table has to be written back to disk in order to obtain the converted file You need to have Profiler 2008 in order to write a trace in the " [Read More]

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]

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]

Many setup scripts for SQL Server include the 3659 trace flag, but I could not find official documentation that explains exactly what this flag means. After a lot of research, I found a reference to this flag in a script called AddSelfToSqlSysadmin, written by Ward Beattie, a developer in the SQL Server product group at Microsoft. The script contains a line which suggests that this flag enables logging all errors to errorlog during server startup. [Read More]