CPU Cost

Like all applications SQL Server uses processor (amongst other things) and knowing how much processor a particular T-SQL statement is using is very useful, particularly during performance turning that code.

SET STATISTICS TIME does just that.

 So what exactly is SET STATISTICS TIME

 SET STATISTICS TIME is a setting you can turn on in your query window (you can also turn it on in options), the idea is that you can turn it on, run your code then turn it off again. This then produces a result set detailing the CPU time the query took. So lets say I want to know how much CPU time my query to select a list of all the databases on my server is taking it would look like this;

SET STATISTICS TIME ON

GO

–This is where your T-SQL code would go

SELECT name FROM sys.databases;

SET STATISTICS TIME OFF

GO

You guessed it, the ‘ON’ line turns it on, and the ‘OFF’ line turns it off. If you don’t turn it off it will remain on until you close the query window (which closes the SPID or Session Process ID).

Where are my results?

So you’ve run the command and all you see are a list of names of the databases in the results pane, well that’s because the results we care about are actually in the ‘messages’ tab so go ahead and click on that. You should see something that looks like this;

SQL Server parse and compile time:

CPU time = 47 ms, elapsed time = 48 ms.

(57 row(s) affected)

SQL Server Execution Times:

CPU time = 16 ms,  elapsed time = 10 ms.

So what’s going on here?

The results are broken in to two parts for each statement, the first is the parse and compile time, this is basically where SQL Server is going through the statement to check for any parsing errors and then compiling the query to a plan. So in the example above we can see it took 47ms of CPU time to parse and compile the command.

The second part of the results is the actual execution times themselves, so that’s how long it took to run the select statement itself.

Each section has two times, CPU and elapsed, both are in millisecond, the difference between the two is the CPU time is the amount of time the CPU spent doing work, the elapsed time is the total time from start to finish. Our query is nice and simple but if you imagine a query that has to get a lot of data from disk then you could see the CPU time being a few milliseconds but the elapsed time being much higher as the data had to fetched from disk. Likewise CPU time could be higher than elapsed time if the query was run across multiple cores due to the query running across multiple cores.

The elapsed time is prone to variation due to other factors, disk I/O etc whereas the CPU time should be relatively constant and allow you to judge performance.

So there you have it you can now tell the CPU footprint of any T-SQL which runs on your server. Next time we’ll have a look at the I/O footprint using SET STATISTICS IO.

Advertisements
Posted in Development, SQL Server

SQL Server’s DAC

In this blog post I’m going to talk about the DAC and how you can enable it and how it may just save you from a very bad day if you ever have trouble connecting to your SQL Server.

So what is the DAC?

The dedicated admin connection, or DAC, allows an administrator to connect to an instance of SQL Server 2005 or higher in order to diagnose problems in the event other connections are not being accepted.

Typically this is useful when your server has a ‘run away’ query or process which is maxing out system resources and resulting in time out of connections. The DAC reserves a small portion of these system resources so that you will always be able to connect in and run any queries you need to get the instance back up and working as normal.As the name suggests, you have to be a member of the SQL sys admin group to connect using the DAC.

So how do we enable the DAC?

The DAC can be enabled by simply connecting to the SQL Server instance and running the following command (as either a sysadmin or serveradmin in SQL Server);

EXEC sp_configure ‘remote admin connections’,1

GO

RECONFIGURE

GO

If you are not familiar with sp_configure it is basically used as a method to make configuration changes on your server and the RECONFIGURE command is used to apply the setting changes. Be very careful using it.

You should get a message back that states;

Configuration option ‘remote admin connections’ changed from 0 to 1. Run the RECONFIGURE statement to install.

The DAC is now enabled on the instance of SQL Server.

And how do we connect using the DAC?

So it’s time to connect to you server using the DAC, this can be done in one of two ways, either via SSMS or via the command line.

Connecting via both is easy, all you need to do is prefix your server name with ADMIN:

So assuming you server is called ServerA, in SSMS you would just put ADMIN:ServerA as the server name and via the command line you would just run;

SQLCmd –S ADMIN:ServerA

That’s it, but remember you can only have 1 DAC connection, this isn’t really a problem from the command line but in SSMS you need to do it via File>New>Database Engine Query and entering the server name there (with the ADMIN:) this makes sure only the query window connects and nothing else.

You can check that you are connected to the DAC by using the following DMV to look at the endpoints.

SELECT session_id FROM sys.dm_exec_connections WHERE endpoint_id = 1

The query should return one record in which the session_id matches yours.

I hope you found this post useful. As a standard practice we enable the DAC on our servers but we don’t use them unless absolutely necessary. The overhead is almost zero so to me it’s a no-brainer, get it enabled.

Posted in Administration, SQL Server
Recent Comments
will on SQL Server’s DAC

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3 other followers