I have following configuration: 1. active/passive cluster, Win2000, SQL2000 2. 4 CPUs in each node, 4GB RAM in each node 3. Disk arrays configured for optimal performance (different arrays for diff things, diff RAID levels etc.) I have few periods during the day when CPU utilization is stuck between 30 and 50% without a single peak on any side. That can last for more than hour. All CPUs are behaving the same way. Response on the client side is very slow. The only process in that period of time that is using CPUs extensively is sqlservr.exe. I've checked deadlocks - nothing. I was running Profiler to check slow queries - nothing. Other perfmon counters seams to be OK (one does give me concern actually - buffer cache hit ratio is actually over 100, around 100.150 or similar?!). Without any performed action, this behaviour stops as sudden as it started and utilization on all CPUs is back on expected 0-100%! Any ideas? Appreciate your help!
How about status of Total Server Memory (KB), pages/sec counters? And how about memory settings on SQL Server? _________ Satya SKJ Moderator SQL-Server-Performance.Com
How about Disk usage during that time? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Almost 1GB of RAM is still available, pages/sec on average < 20. SQL Server is set to use memory dynamically. quote:Originally posted by satya How about status of Total Server Memory (KB), pages/sec counters? And how about memory settings on SQL Server? _________ Satya SKJ Moderator SQL-Server-Performance.Com
Is that total server memory is constant or variable? _________ Satya SKJ Moderator SQL-Server-Performance.Com
Disk usage is fine - avg. queue lenght is less than 0.1 and %disk time is 1.5%. quote:Originally posted by LuisMartin How about Disk usage during that time? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Any parallel jobs or process are running during this slow transition period? How about DBCC checks? _________ Satya SKJ Moderator SQL-Server-Performance.Com
Which counter do you actually have in mind? Memory:Available Bytes is variable and is between 500MB and 1GB. Thanks for help quote:Originally posted by satya Is that total server memory is constant or variable? _________ Satya SKJ Moderator SQL-Server-Performance.Com
No, server is dedicated to SQL and sqlservr.exe is the only process that takes resources. I haven't tried DBCC checks - what should I look for? quote:Originally posted by satya Any parallel jobs or process are running during this slow transition period? How about DBCC checks? _________ Satya SKJ Moderator SQL-Server-Performance.Com
I'm concerned about total server memory (KB) counter. So it seems no DBCC checks are in place, what is the size of the database and userbase. _________ Satya SKJ Moderator SQL-Server-Performance.Com
Why not DBCC DBcheck or checkalloc to see if there is some problem? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
0 allocation errors and 0 consistency errors quote:Originally posted by LuisMartin Why not DBCC DBcheck or checkalloc to see if there is some problem? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Database Grow, is set automatic 10%?. If yes and Database is big, may be SQL has extra work to extend it. In this case I suggest to leave automatic but with fix amount, less than 10%. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
automatic off quote:Originally posted by LuisMartin Database Grow, is set automatic 10%?. If yes and Database is big, may be SQL has extra work to extend it. In this case I suggest to leave automatic but with fix amount, less than 10%. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
7.5GB quote:Originally posted by satya What is the size of database? _________ Satya SKJ Moderator SQL-Server-Performance.Com
Then DBCC DBREINDEX during less traffic on the database and check the performance. _________ Satya SKJ Moderator SQL-Server-Performance.Com
Or DBCC INDEXDEFRAG if you can't run DBCC DBREINDEX on line. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
indexes are recreated each night quote:Originally posted by satya Then DBCC DBREINDEX during less traffic on the database and check the performance. _________ Satya SKJ Moderator SQL-Server-Performance.Com
How about Update Statistics? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
How about Update Statistics? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
automatic quote:Originally posted by LuisMartin How about Update Statistics? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Automatic in database setting, I gess. But I suggest to Upadate Statists full on night or non peek hours at least every week. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
I have "Auto Update Statistics" database setting ON. Are you sugessting something in addition to that? Can you be more precise please? Thanks. quote:Originally posted by LuisMartin Automatic in database setting, I gess. But I suggest to Upadate Statists full on night or non peek hours at least every week. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Yes, in addition to that. Schedule a job with sp_updatestats. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
Do you have any other database settings like Auto_Close or Auto_Shrink? _________ Satya SKJ Moderator SQL-Server-Performance.Com
What I'm doing each night is running Data Maintenance Plan with option Reorganize data and index pages. So statistics are updated. quote:Originally posted by LuisMartin Yes, in addition to that. Schedule a job with sp_updatestats. Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
No Auto_Close, no Auto_Shrink. quote:Originally posted by satya Do you have any other database settings like Auto_Close or Auto_Shrink? _________ Satya SKJ Moderator SQL-Server-Performance.Com
Maintenance plan defaul is update statistics 10%. Do you change this value to 100%? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
When you choose Reorganize data and index pages then Update statistics option is actually disabled. quote:Originally posted by LuisMartin Maintenance plan defaul is update statistics 10%. Do you change this value to 100%? Luis Martin ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell
When DBREINDEX (re-org of data/index) is selected it automatically updates the stats. _________ Satya SKJ Moderator SQL-Server-Performance.Com
I guess I had the same problem. I ran sp_updatestats (as suggested by people in here). This really works. When you first create a table, SQL Serve starts gathering statistics on it and uses it to answer the queries. SQL Server maintains this statistcis on each table and when user run queries, SQL Server uses this statistics to guess the number of rows to be retrieved by the query. Hence more accurate the statistics are, more fast the query execution is. This statistics get out-of-date gradually even if you set the autostatistic mode on (my personal experiance). So it's better to keep statistics updated from time to time. Cheers...
Yes, in case if you don't maintain DBCC checks on the database. _________ Satya SKJ Moderator SQL-Server-Performance.Com