October 15, 2014 at 9:46 am
Thanks a lot Grant
October 15, 2014 at 10:29 am
Ratheesh.K.Nair (10/15/2014)
I have got around 130 trace files now.In between the CPU came down to 13 at 7:54 and back to 95 again i have trace in between those times.
Can anyone help me to pin point the cause these traces?
Yes, I certainly can. But not via a forum thread. 😎
Run your profiler output through DBSophic.com's free Qure tool. It will give you aggregate analysis of what it hitting your server hardest by multiple metrics. AMAZINGLY helpful product!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 15, 2014 at 10:32 am
GilaMonster (10/15/2014)
TheSQLGuru (10/15/2014)
A proper profiler-to-local-disk script can be run to capture heavy CPU users with very little overhead on the system.And an extended events session will have even less.
Can't run those through DBSophic's Qure though. And many (most?!?) SQL Server users still have little to now experience with XEvents or the XML stuff you need to mess with to make use of them. Besides, if the overhead of server-side-to-local-disk tracing is low enough you can use it on servers with 1K/sec activity a bit less for XE overhead doesn't matter. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 15, 2014 at 11:03 am
TheSQLGuru (10/15/2014)
GilaMonster (10/15/2014)
TheSQLGuru (10/15/2014)
A proper profiler-to-local-disk script can be run to capture heavy CPU users with very little overhead on the system.And an extended events session will have even less.
Can't run those through DBSophic's Qure though. And many (most?!?) SQL Server users still have little to now experience with XEvents or the XML stuff you need to mess with to make use of them. Besides, if the overhead of server-side-to-local-disk tracing is low enough you can use it on servers with 1K/sec activity a bit less for XE overhead doesn't matter. 🙂
But, we're in 2012, so we have a gui that can do grouping and all sorts of nice stuff, no XML needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 15, 2014 at 11:34 am
Grant Fritchey (10/15/2014)
TheSQLGuru (10/15/2014)
GilaMonster (10/15/2014)
TheSQLGuru (10/15/2014)
A proper profiler-to-local-disk script can be run to capture heavy CPU users with very little overhead on the system.And an extended events session will have even less.
Can't run those through DBSophic's Qure though. And many (most?!?) SQL Server users still have little to now experience with XEvents or the XML stuff you need to mess with to make use of them. Besides, if the overhead of server-side-to-local-disk tracing is low enough you can use it on servers with 1K/sec activity a bit less for XE overhead doesn't matter. 🙂
But, we're in 2012, so we have a gui that can do grouping and all sorts of nice stuff, no XML needed.
But does the grouping standardize the parameters for all calls (while still allowing you to view individual calls with their parameter values)? Allow aggregation for sums or averages and ordering by all the important tuning metrics? Not being snarky here, btw. I honestly don't use it so I just don't know what has been added.
BTW, I will add that Idera has a freebie tool that is kinda like Profiler for XE tuning (https://www.idera.com/productssolutions/freetools/sqlxeventprofiler). Just wanted to add that to the thread for posterity's sake. Very limited capabilities too compared to Qure...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 15, 2014 at 11:57 am
TheSQLGuru (10/15/2014)
And many (most?!?) SQL Server users still have little to now experience with XEvents or the XML stuff you need to mess with to make use of them.
Then they need to get that experience before trace is completely removed and they're forced the hard way to learn XE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2014 at 12:18 pm
TheSQLGuru (10/15/2014)
Grant Fritchey (10/15/2014)
TheSQLGuru (10/15/2014)
GilaMonster (10/15/2014)
TheSQLGuru (10/15/2014)
A proper profiler-to-local-disk script can be run to capture heavy CPU users with very little overhead on the system.And an extended events session will have even less.
Can't run those through DBSophic's Qure though. And many (most?!?) SQL Server users still have little to now experience with XEvents or the XML stuff you need to mess with to make use of them. Besides, if the overhead of server-side-to-local-disk tracing is low enough you can use it on servers with 1K/sec activity a bit less for XE overhead doesn't matter. 🙂
But, we're in 2012, so we have a gui that can do grouping and all sorts of nice stuff, no XML needed.
But does the grouping standardize the parameters for all calls (while still allowing you to view individual calls with their parameter values)? Allow aggregation for sums or averages and ordering by all the important tuning metrics? Not being snarky here, btw. I honestly don't use it so I just don't know what has been added.
BTW, I will add that Idera has a freebie tool that is kinda like Profiler for XE tuning (https://www.idera.com/productssolutions/freetools/sqlxeventprofiler). Just wanted to add that to the thread for posterity's sake. Very limited capabilities too compared to Qure...
You don't have to sweat that. You can either group on the object id for stored procedures or you can group on the query hash for batches (incomplete on that one I know).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2014 at 8:55 am
Thanks Everyone for the inputs.
October 17, 2014 at 7:29 am
Hi Experts,
is it possible to have database timeout because of running profiler?
October 17, 2014 at 7:32 am
If you're running the Profiler GUI, absolutely, and you're lucky if that's all it does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2014 at 7:34 am
Ratheesh.K.Nair (10/16/2014)
Thanks Everyone for the inputs.
Databases don't time out. Does the Profiler gui put a substantial load on a system that can cause memory issues, leading to blocking causing timeouts? Yes. Absolutely. You really should not be running the Profiler gui against a production system, ever. This is doubly true of a 2012 system. You should be using extended events, not Profiler or trace.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2014 at 3:42 pm
I had a brief experience with a server showing High CPU usage. With the basic sp_who2 I found the suspect process ID and plugged it into
DBCC inputBuffer(pid)
That clued me to the command being issued and the application at fault. Before I only got ".Net SqlClient Data Provider" from sp_who2. It may / may not help you but it was my quick and dirty way of isolating the issue with high CPU usage on one of our servers.
----------------------------------------------------
October 31, 2014 at 9:12 am
No sure if I am too late for this, but this happened to me two weeks ago. After checking all the usually suspects, I ran Adam Machanic's sp_whoisactive. Even with a 98% CPU utilization, it ran quickly. I copied the results into an excel sheet and sorted by CPU. It gave me not only the user that were causing the problem, but also the code they were, not coincidentally, both running.
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
October 31, 2014 at 9:19 am
I too use Adam Mechanics sp_whoisactive. I was able to cut my cpu usage significantly. Good tool to learn how to use
Kathy
November 3, 2014 at 8:13 am
Also check anti virus scan!!
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply