SQL Profiler is a wonderful tool provided along with SQL Server Setup to monitor activities running on a SQL Server at any point of time. However people might wonder I get the same when I run sp_who or sp_who2 and what extra one gets by using profiler. Any other monitoring tool would provide process or connection level information and we have to use multiple steps to locate what activity is performed by a particular process and that is where SQL Profiler is handy.
Now let us know see how to use SQL Profiler and when to SQL Profiler
Start Menu-> Run and type profiler, in the UI provide server/User and password and then choose default trace and click on run and we could commands which are fired against the server.
The basic information we receive from profiler are TextData, Application Name, LoginName, Reads, Writes, CPU, Duration, SPId, StartTime, EndTime of which main parameter is TextData which shows the data sent from the client to the server along with the command executed.
Say for example I execute say a procedure Proc_test with Parameter1 value as "Test" and Parameter2 value as 120 then the TextData would show value as
EXEC Proc_Test "Test",120
This is very useful for troubleshooting as many of times we end up with application users saying its a query or procedure bug and we find finally that application has passed a hard coded value as the application developer forgot to change the code or parameter values were jumbled. So first thing any database developer needs to do when a bug or issue is logged as saying no rows returned or wrong data fetched from procedure would be to run profiler and confirm correct values are passed to database before analyzing the issue which would save lots of human errors and speedy recovery of issues.
There are other useful information which are available which help us troubleshoot from performance perspective
Reads - How much of reads is performed for each statements which would help us determine how much data is read from disk instead of buffer
Writes - How much of Writes is performed for each statement from which we can again know how much of IO is being consumed
CPU - CPU is the amount of CPU time the query consumed
Duration - Duration is the amount of time it took to execute the statement.
Needless to say that duration is the first parameter to see and locate statements which has more duration and then take the statement from Textdata and analyze further on whether reads and writes for those statements are appropriate or tuning is required from Index perspective or re-write query logic. To get statement level we need to use SQLBatchCompleted as RPCCompleted would just return procedure level and not statement level.
And also as a responsible developer we need to understand that SQL Profiler also consumes some amount of CPU on the server as it monitors the server continuously and
so it is restricted to only sysadmins.
Also we need to use SQL Profiler cautiously and not choose this for all troubleshooting issues , so talk to the DBA when in real need and DBAs can give permission to user for
running the same.
One more good option we get from profiler would be save these traces as a trace file or to SQL Server table so we can analyze these traces in future for troubleshooting.
In the coming weeks we would see some more features on the SQL Profiler to leverage this tool in a still better way to solve many of our issues…………
No comments:
Post a Comment