Hello!<br /><br />We have a server with 3 2.8GHz xeon cpu<img src='/community/emoticons/emotion-7.gif' alt=':s' />, 3GB ram.<br /><br />The server is running a web application and recently we made a change, but the results is not quite what I expected, can someone help me figure this out?<br /><br />We changed one table, so that we made the clustered index (also primary key) on that table only have one column instead of two. The change gave us better results in all of our queries, since all of our queries were just asking on one column instead of two.<br /><br />We run the performance monitor with a frequence of 900 seconds.<br /><br />Before the change our max on CPU were around 70%, and avarage was 42%. <br />After the change the MAX raised to 93% and avarage was about the same (43%).<br /><br />When I run a trace I clearly see the improvement of speed, since the queries that earlier were taking up time is now running very quickly.<br /><br />Before the change we had about 170 transaction per second, and after the change it was raised to 197 TPS.<br /><br />The biggest change is the user connection value. Earlier we had max 691 user connections, but after its 397.<br /><br />We still have the same amount of queries, and the same amount of users connected.<br /><br />I can see that the user connection probably is lower because of that the web pages loads more quickly and does not have to stay connected that long anymore.<br /><br />But why is the MAX CPU higher now? Earlier when the queries took much longer, shouldnt it have been working on the queries then? Or can it be that it was alot of waiting and other things that it had to handle before?<br /><br />The result for the end users is what we expected. Its alot faster, so that is good.<br /><br />This is a good example of that you should not only check the CPU load when trying to fix performance issues.
Besides mail I sent to you, what in going on with Disk and memory? What Monitor Profiler show about that? Luis Martin Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
a query's duration is normally not just bound by the cpu time, often it is as a result of additional non-cpu work such as reading/writing stuff to disk/cache.<br /><br />So yes a quick query may be barder on a CPU, ideally you want the CPU to peak as high as possible as long as it can still cope well with the throughput. There is no point having a CPU peak at say 30%, if it is then you would have over spec'd the box quite severely <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
LuisMartin, what counters are you interested of? Our database is rather small (1.8 GB), so we are lucky to have it all in the memory. Twan, yes of course, just courious why the cpu wasnt maxed out before, but I think I understand that now. Thanks!
Performance Monitor: Physical disk: Avg. Disk Queue Lenght. Memory: Pages/sec. SQLServer Buffer: Buffer Cache Hit Ratio, Page life Expectancy. SQLServer Memory Manager: Target Server Memory, Total Server Memory. Luis Martin Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
How often you update the TABLE/INDEX statistics? How often you schedule the DBREINDEX jobs, if any? How about SQL Memory configuration, dynamic or fixed? How about SQL resource usage, any other applications sharing the tasks? Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
The statistics are on "auto create/update". I recreate my index every night. Memory is dynamic (0 - 3071) The server is "stand alone" for and works for this database only.
Collect the PERFMON stats as mentioned by Luis above for better assessment, I believe this could be issue on Hard disks (if you're using RAID configuration). Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
If you are rebuilding index every night, statistics will update also, so to get a litle more performance turn auto update off. Luis Martin Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
LuisMartin: We run our maintenance plan with these parameters: "-RebldIdx 10 -RmUnusedSpace 50 10" I have tried to read the BOL to find that rebuilding indexes also update my statistics, but I cannot find that, however I did find a parameter called "UpdOptiStats", which updates the statistics.
Rebldidx 10 means: Reorganize data and index pages and change free space per page to 10%. And that is for all database. Because you database size, I think is OK. When you have a big database, is better to reindex when is necesary (image 50Gbyte database, and reindex all database, may be will take more than 12 hours) i.e when fragmentation is below 80%. You can find some store procedures in this forum to reindex only in those cases. Anyway, rebuild mean also update statistics. Luis Martin Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
True while rebuilding indexes it will update the statistics on sysindexes which will be used for optimum performance. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Where can I read that my statistics are updated when rebuilding my indexes? Not that I doubt you, just that I want to understand how this works since I cannot find anything about that in BOL.
Hi! Here is my statistics. These results are over 24 hours with an update interval of 10 minute. SQL, pages/sec: 31 min 464 avg 723 max Disk queue: 0,875 min 1,55 avg 3,922 max Buffer Cache: 99,776 min 99,862 avg 99,891 max Page Life exp: 1911 min 18243 avg 33950 max
There is no documentation, but after rebuild any index run: STATS_DATE Returns the date that the statistics for the specified index were last updated. Syntax STATS_DATE ( table_id , index_id ) and you can see that. Luis Martin Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I cannot reference the document as I read the same on one of the Technet resources, I will definetly refer the link if I get a chance to find it. To divide the topic, UPDATE STATISTICS is an intermittent solution to get optimum performance on the highly updated tables and REBUILD INDEXES is more independent on the INDex name(s) and as explained it will update the necessary information in SYSINDEXES table once the process is finished. DBCC DBREINDEX is an offline operation. While this operation is running, the underlying table is unavailable to users of the database. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
