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 |
|
Kimbie
Starting Member
2 Posts |
 Posted - 2008-10-20 : 11:49:32
|
| We have a HP ProLiant ML310 running as our ISA server.The spec of the server is: P4 2.8Ghz [No HT], 3Gb RamWe are running Windows 2003 SP2, SurfControl 5.5 SP3, Microsoft SQL Desktop Engine, ISA 2006 SP1We have approx 500 users running through the ISA, however when we were looking into a proble, we have discovered that the process SQLServr.exe spikes to 100% for a second or two, every 10 - 15 seconds the process will spike at 100% and drop back down to ~5%Having read some articles about SQL server monitoring I have used the Performance monitor and looked at both SQLServer\Batch Requests and SQL Compilations. Just after the sql spikes to 100% the Batch requests\sec shoots up to 187 and the SQL Compilations/sec rise to 18Any help is appricatedCheersKimbie |
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-21 : 12:48:09
|
| Do you have any scheduled batch runnign at that interval or many users connecting to SQL executing many batches at the same time.How big is your user database? Have you ever updated statistics?Next time it occurs conect to SQL Server using osql utility as follows.Go to command promt type 'osql -E -S<name of your machine\name of you SQL Server instance it is named instance)'You will get following promt '1>'Type followng queryselect top 2 * from sysprocesses order by CPU descgothis tells you top 2 queries in terms of CPU consumption.This should give you clue on what is going on.You can also type following command at command promt to update statistics with full scansp_msforeachtable 'update statistics ? with full scan'goHope this helps |
 |
|
|
Kimbie
Starting Member
2 Posts |
Posted - 2008-10-22 : 05:33:31
|
Thanks for replying.As far as I know no batches are running, other than install the software we have not touched the configuration of the sql/msdeI am not sure what you mean by user database, the only software that connects is our webfiltering software using our system admin account.I have never updated the statisticsI ran the commant you suggested and have pasted those below spid \kpid \blocked\waittype\waittime \lastwaittype waitresource \dbid \uid \cpu physical_io \memusage \login_time last_batch \ecid \open_tran\status sid hostname program_name \hostprocess cmd nt_domain nt_username \net_address net_library loginame context_info sql_handle \stmt_start \stmt_end------\------\-------\--------\-----------\-------------------------------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ----------------------------------------\------\------\----------- --------------------\-----------\----------------------- -----------------------\------\---------\------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------ ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ --------------------------------------------------------\----------- ---------------- ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ --------------------------------------------------------\------------ ------------ ------------------------------------------------------------------------ -------------------------------------------------------- ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------ ------------------------------------------\-----------\----------- 52\ 0\ 0\0x0000 \ 0\WRITELOG \ 5\ 1\ 1045905 8150\ 285\2008-10-22 07:47:45.717 2008-10-22 09:34:47.620\ 0\ 0\sleeping 0x0105000000000005150000009B0BC11EBE043E32D33EA5365206000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000 INTEGRA SurfControl Web Filter \256 AWAITING COMMAND ARCS sysadmin \000BCDCF97FF LPC ARCS\sysadmin 0x0000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000\ 0\ 0 51\ 0\ 0\0x0000 \ 0\PAGEIOLATCH_SH 5:1:184 \ 5\ 1\ 1266 62\ 910\2008-10-22 07:47:54.227 2008-10-22 07:48:11.363\ 0\ 0\sleeping 0x0105000000000005150000009B0BC11EBE043E32D33EA5365206000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000 INTEGRA SurfControl Web Filter \256 AWAITING COMMAND ARCS sysadmin \000BCDCF97FF LPC ARCS\sysadmin 0x0000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000\ 0\ 0(2 rows affected)1> |
|
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-23 : 04:23:05
|
| Looking at the output, I can see that SPID 52 is using about 17 mts (1045905 milli seconds) of CPU tiw which is lot. This batch is coming from 'SurfControl Web Filter' application. It is in the state 'AWAITING COMMAND' , that means it not runnign anythign at the time this output was taken. The CPU time was taken while it was runnign something.You need to add another step to this script, you need to find out what this SPID is runnig. Obviuosly, you need to find the TOP CPU consumign SPID again when the issue occurs next.So the commands you need to run areSelect top 2 * from sysprocesses order by CPU descgoOnce you get output, note the SPID value for first row and type second commandDBCC INPUTBUFFER (SPID number here)The above command should give you what is being run by that SPID (Server process id). You can then take that statement/stored procedure whatever that might be to your development team (or application vendor) and ask them why this is taking so much CPU.Troubleshooting beyond this will require indepth data collection and analysis (getting execution plan etc)Have you updated statistics? |
|
|
|
MidoElmasry
Starting Member
1 Post |
Posted - 2010-03-26 : 07:51:27
|
| Kimbie, I am facing the same problem, did you find a final solution ?Waiting your reply.Thanks in advance. |
|
|
|
|
|
|
|
|
SQLServr 100% CPU spike