October 8, 2012 at 7:10 am
We have problems doing a tuning profile in SQL Server when we try to do the profile workload on a complete database. It is a new SQL Server 2012 installation and the same database on the old server (SQL Server 2005) profiled without any problems on the complete database.
Now when we profile wait stats pile up seriously on TRACEWRITE, so much that our system stops responding.
The new server with SQL Server 2012 performs effectively 30-200% faster then the old, we testet the IO-system which can handle twice the IO easily. The CPU are also a factor 2, old server is 8 cores then new i 32 cores.
Only thing that only performs slightly better on the new server is memory access (probably because of numa).
Before profiling the server is only on 8-10% CPU load, and while we profile maybe 20%.
The database we try to profile is still set to 2005 compatibility level.
Are there any know issues with the profiling in SQL Server 2012 or even 2008 as we skipped that version?
Any help will be appreciated.
October 8, 2012 at 8:08 am
Don't use the profiler GUI on a busy production server (that's in any edition of SQL). It's got a high overhead. Use server-side traces or, in SQL 2008 and 2012, use Extended Events.
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 9, 2012 at 1:26 am
Thank you for the answer, but i don't think the server is that busy. We can do the same on a much slower machine, with the same database.
We also tried using the GUI on another SQL Server without lock, or dumping the workload into a table. It is not the GUI which blocks eveything, but the trace itself inside SQL Server.
There must be some issue, as this worked fine on SQL Server 2005 on another much slower server.
October 9, 2012 at 3:38 am
Ok, let me rephrase that.
Don't use Profiler GUI against a production server. It is known to be cause issues (and that's all the way from SQL 2000 or earlier). For production, use server-side traces and write to a fast, local drive or, on SQL 2008 and 2012, use Extended Events.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply