Just thought I would post this info as I have been talking to some people that did not have all the MS SQL tools installed and then did not know how to get performance information out of the server … maybe just maybe other people in the world would have a similar question :o) ….
When you are developing applications running on databases you will often want to know what is actually taking time in the database.
If you are running on MS SQL Server 2005 (or MS SQL Server 2005 Express) you have a couple of ways to get to the data:
· SQL Server Profiler, this is my tool of choice if it is installed on the machine.
· Object Execution Statistics report, this may be a little more unknown. If you so not have the profiler you can still run the report, with this report you can get to some of the same data as if you are running a profile, this time presented in a nice report. You can get to the report in Microsoft SQL Server Management Studio in the top of the summary page.
· Select it from the system views. If you don’t have any tools except the server you can still get to some information. When SQL Server runs it saves some execution statistics that you can access through system views (the report retrieves data from these views). The SQL Statement below is able to pull quite interesting data from a database…
Select s3.name as [Obj Name]
,s3.type as [Obj Type]
,(select top 1 substring(text,(s1.statement_start_offset+2)/2,
when s1.statement_end_offset = -1
end - s1.statement_start_offset) /2 )
sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement]
,((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time]
,((total_logical_reads+0.0)/execution_count) as [avg_logical_reads]
,((total_logical_writes+0.0)/execution_count) as [avg_logical_writes]
/execution_count + (total_logical_reads+0.0)/execution_count)
,total_logical_writes + total_logical_reads+0.0 total_logical_IO
,last_logical_writes +last_logical_reads+0.0 last_logical_IO
,min_logical_writes +min_logical_reads+0.0 min_logical_IO
,max_logical_writes + max_logical_reads+0.0 max_logical_IO
from sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(sql_handle) as s2
inner join sys.objects s3 on ( s2.objectid = s3.object_id )
left outer join sys.schemas sch on(s3.schema_id = sch.schema_id)
where s2.dbid = db_id()
order by s3.name, s1.sql_handle
To run such a statement with no special tools installed I often use SqlCmd. With this tool I can quickly run a script on the database from the command prompt with something like this:
SQLCMD -S"(local)\MSSMLBIZ" -d"sampleproductcompany" -Q"select * from CustomerAccountTable" > select.txt
Naturally you have to put in your own server and database information ;o) As you can see it is not a hard thing to use, simply state the server (-S) the database (-d) and a SQL query (-Q) or if your query is big you can put it in a file and execute it with a –i parameter instead of the -Q