Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2008-06-17 : 13:32:33
|
| sql server was using a lot of the cpu so I ran a trace.Now i see the results and there are lines with high cpu number with textdata NULL and AppicationName Internet Information Serviceshow can i figure out what this is? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 13:36:16
|
| Don't even bother looking at null data in TextData. CPU, Reads, and Writes are cumulative numbers since they connected. Ignore any null data in TextData. Here is my default query:SELECT TOP 1000 Duration/1000000.0 AS Duration, TextDataFROM TraceTable (or better a tracefile)WHERE TextData IS NOT NULLORDER BY Duration DESCTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-06-17 : 13:58:05
|
| so all these are small... How else can I figure out how to get it to use less of the cpu? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-06-17 : 14:13:48
|
| basically i have sql server installed on a server in an office where they also use the server for basic needs.Now at times they complain that sql is using up almost all the cpu. I finally got profiler installed - and did a trace but I see the database was just used a lot - not that any specific query hung.Is there any solution to this? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 14:19:33
|
| Do not run SQL Profiler on the database server. Run it on your client machine and save the results to a file. If you run it on the database server and/or save it to a table, you are adding to the performance problem. I've actually taken down production by doing it this way. See this for more information on SQL Profiler best practices: http://msdn.microsoft.com/en-us/library/ms979207.aspxHow did they determine that SQL Server was using "almost all the cpu"? What are the hardware details? How big is the database? What is the average CPU of the database server during peak times? What else is running on the database server? IIS? Other client applications?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2008-06-17 : 15:10:25
|
| I can tell it's using almost all the cpu based on task manager. database is very big.. during peak times it seems to be using at least 50%.There is iis and basic client applications on the server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-17 : 15:12:03
|
| Sounds like you've got a hardware bottleneck due to inadequate hardware and also because you don't have a dedicated database server. There's not much you can do on the SQL side when you've got an environment like this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
analyzing sql profiler results