Why is it so slow ?
-
Tuesday, December 11, 2012 3:08 PM
I have a simple query like this :
select cMaj.CatMaj,cInt.CatInt, MontantVente=sum(meTotalLigne)
from VEN_Factures f
join VEN_TypeAnalyse ta on (ta.idTypeAnalyse = f.idTypeAnalyse)
join INV_Entrepots ent on (ent.IdEntrepot = f.IdEntrepot)
join VEN_FacturesDet d on (d.IdFacture = f.IdFacture)
join INV_Produits p on (P.IdProduit = d.IdProduit)
join INV_CatMaj cmaj on (cmaj.idCatmaj = p.IdCatMaj)
join INV_CatInt cint on (cint.idCatint = p.IdCatInt)
join INV_CatMin cmin on (cmin.idCatmin = p.IdCatMin)
where f.DateMAJ >= '2012-11-01' and f.dateMaj <='2012-11-30' and cMaj.CatMaj='CP'
and ta.TypeAnalyse='CA' and ent.NoEntrepot not in ('26','20','16','15','14','13')
group by cMaj.CatMaj,cInt.CatInt
order By 1,2,3The table VEN_factures contains something around 3 000 000 records, i understand there are quite a few records, i also have some indexes on table , one of them being
CREATE NONCLUSTERED INDEX [Idx_VEN_Factures_DateMaj]
ON [dbo].[VEN_Factures] ([DateMAJ])
INCLUDE ([IdFacture],[IdEntrepot],[IdTypeAnalyse])My question is :
Why do i see something like this when looking at the ressource monitor ?
Shouldnt i see the disk topping at their maximum read, something like 100 MB/s at least ?
I can barely see the cpu running, and the disk running . I can't understand . What is wrong ?
k.r.
- Moved by Tom PhillipsModerator Tuesday, December 11, 2012 4:36 PM Query tuning question (From:SQL Server Database Engine)
All Replies
-
Tuesday, December 11, 2012 8:14 PM
Disk/memory/cpu usage are what I would expect for a query on a large dataset. Note that most queries are very light on cp usage - disk is the bottleneck as seen here.
The nn-clusteed index covers the query in regard to table VEN_Factures, as you knew.
Is there an index on (cMaj.CatMaj,cInt.CatInt)? if not, the entire data set must needs be sorted to satisfy the order by clause, which is O(N.log(N)) complexity, and could take a while.
It is puzzling that your network is moderately busy - You weren't downloading something while running this were you? That would tie up disk and interfere with SQL Server performance.
"Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
We are here to learn, to share knowledge, and to earn points; all in about equal measure.
- Edited by Pieter Geerkens Tuesday, December 11, 2012 8:15 PM
-
Tuesday, December 11, 2012 9:20 PM
Thanks for answering,
The network usage is for the most part my remote desktop session, there might have been something else in the instant i captured the screen. But nothing prevalent.
The record count for the table CatMaj, Int and Min are 18, 193 and 670, and the total record count output from the query is 9 records
When i re execute the same query now , it takes about 9 seconds to produce the 9 records. Where are those caches ?
But my question was more in the "why i do not see at least a disk (whichever one is causing a bottleneck) at its maximum speed" ? Is there something wrong in my system ?
Why would the "3 MB/Sec" as seen on the Raid 5 D: be the bottleneck ? If i manually copy files on that disk , i can see speed ranging from 150MB/sec to 350 MB/sec
From what i understand, the db has to read the data from the raid5 D: with the index (712758 records), produce a temporary output in the temp db, which is split in two raid5 D: and G:, and log is on F:, and then sort the output (9 records) and output it .
Should'nt a disk be at its top speed ? , slowing all the other traitment ? Beeing probaby D: which would have to read and write a portion of the tempdb ?
But that is not what i see, i see a slow 6 MB sec for all disk combined ! What is the DB engine doing ??? Or is it that i have a false impression of a cpu cycle speed that is much much more slower than was i expected? Or what ???
The cpu is a Intel Xeon E5-2609 @ 2.4 GHz
I have 72 Gb RAM installed
This is a SQL Server Standard Edition.
the disk are as follow :
C: win server 2012 , RAID 1 Mirror
D: Data, Raid 5
F: Logs, Raid 1 Mirror
G: Data, Raid 5
Please help me understand
k.r.
- Edited by karlr30 Tuesday, December 11, 2012 9:39 PM
- Proposed As Answer by Basit Farooq Wednesday, December 12, 2012 11:19 AM
-
Wednesday, December 12, 2012 12:56 AMYou are forgetting about disk-latency; that is the time it takes for the platters to rotate the correct sectors underneath the read/write heads, and/or for the heads to slide to the correct cylinder. Disks are not random-access. You will only ever approach maximum bandwidth for your disks when all data is contiguous, or nearly so. This circumstance can only ever be approached when the DB engine is scanning a clustered index.
"Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
We are here to learn, to share knowledge, and to earn points; all in about equal measure.
- Edited by Pieter Geerkens Wednesday, December 12, 2012 12:56 AM
-
Wednesday, December 12, 2012 1:08 AMModerator
Can you try the following:
Place indexes on all FOREIGN KEYs and WHERE condition columns.
Place clustered index (if available) on the f.DateMAJ column.
If this is a business critical query, then consider changing the clustered index over to DateMaj.
Optimization article:
http://www.sqlusa.com/articles/query-optimization/
REBUILD indexes every weekend with FILLFACTOR for dynamic tables.
UPDATE STATISTICS every night.
It would be helpful if tempdb data is on dedicated RAID1. Even better if tempdb log is on dedicated RAID1.
Hard disk configuration:
http://www.sqlusa.com/bestpractices/configureharddisk/
Kalman Toth SQL 2008 GRAND SLAM
New Book: SQL Programming & Database Design Using Microsoft SQL Server 2012
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Wednesday, December 12, 2012 1:29 AM
-
Wednesday, December 12, 2012 9:00 AM
Hi,
1.Check the Fragmentation level of all tables If it is high then de-fragment it.
To identify the fragmentation level use below URL.
http://gallery.technet.microsoft.com/List-of-all-tables-with-b1586515
2.IF possible change the Isolation level to read Uncommitted (i.e Implementing NOlOCK query hint).By doing this blocking and locking will be avoided but dirty ready should be accepted
See below URL for transaction isolationlevel
http://msdn.microsoft.com/en-us/library/ms173763.aspx
3.While you are running the query check if any locking or Blocking happens.Once you identify fix the locking and blocking
http://gallery.technet.microsoft.com/Blocking-And-Locking-881e06c7
4.If possible provide execution plan for Further assistant.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
-
Wednesday, December 12, 2012 11:18 AM
Hi K.r,
Looking at the stats it appears to me that you have problem with a missing indexes. Ensure that the query is index properly. If you believe that your query is properly index than the problem could be with the index fragmentation or your query statistics which might be outdated. I suggest you to the following to reduce fragmentation.
1) Rebuld/Reorganise indexes based that is fragmentation level. Microsoft recommends reorganizing index if an index fragmentation is between 5-30% and rebuilding index if the index fragmentation is over 30%. For more details about reorganize and rebuild Indexes refer to Microsoft Books Online at http://msdn.microsoft.com/en-us/library/ms189858.aspx. Feel free to use my sp_RebuildIndex procedure, that rebuilds indexes based on fragmentation:
Rebuild indexes based on fragmentation – sp_RebuildIndex
2) After you completed the task 1, execute sp_updatestats to update statistics on the database that needs updating.
Hope this will resolve this performance issue.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.com
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.- Proposed As Answer by Basit Farooq Wednesday, December 12, 2012 11:18 AM
-
Wednesday, December 12, 2012 7:25 PM
You are forgetting about disk-latency; that is the time it takes for the platters to rotate the correct sectors underneath the read/write heads, and/or for the heads to slide to the correct cylinder. Disks are not random-access. You will only ever approach maximum bandwidth for your disks when all data is contiguous, or nearly so. This circumstance can only ever be approached when the DB engine is scanning a clustered index.
"Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
We are here to learn, to share knowledge, and to earn points; all in about equal measure.
Thanks again,
But are you sure ? 3 MB/sec seems too much slower for that. I would think that even if i needed to read all the data between here and the moon, it would be faster than 3 MB/sec.. Maybe at least 30 MB/sec if when sequential i go at up to 350 MB/sec
no ?
I am rebuilding all the indexes right now, and i can see speed varying from 23 MB/sec to 50 MB/sec.
k.r.
- Edited by karlr30 Wednesday, December 12, 2012 8:45 PM
-
Wednesday, December 12, 2012 9:00 PM
Hi K.r,
Looking at the stats it appears to me that you have problem with a missing indexes. Ensure that the query is index properly. If you believe that your query is properly index than the problem could be with the index fragmentation or your query statistics which might be outdated. I suggest you to the following to reduce fragmentation.
1) Rebuld/Reorganise indexes based that is fragmentation level. Microsoft recommends reorganizing index if an index fragmentation is between 5-30% and rebuilding index if the index fragmentation is over 30%. For more details about reorganize and rebuild Indexes refer to Microsoft Books Online at http://msdn.microsoft.com/en-us/library/ms189858.aspx. Feel free to use my sp_RebuildIndex procedure, that rebuilds indexes based on fragmentation:
Rebuild indexes based on fragmentation – sp_RebuildIndex
2) After you completed the task 1, execute sp_updatestats to update statistics on the database that needs updating.
Hope this will resolve this performance issue.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.com
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.But if it was a missing index, that would imply that i would just be reading a lot more data than what is really needed .. so that would be seen on speed with the performance monitor with an higher MB/sec .
Right ?
"Premature optimization is the root of all evil." - Knuth
k.r.
- Edited by karlr30 Wednesday, December 12, 2012 9:10 PM
-
Wednesday, December 12, 2012 9:13 PM
Can you try the following:
Place indexes on all FOREIGN KEYs and WHERE condition columns.
Place clustered index (if available) on the f.DateMAJ column.
If this is a business critical query, then consider changing the clustered index over to DateMaj.
Optimization article:
http://www.sqlusa.com/articles/query-optimization/
REBUILD indexes every weekend with FILLFACTOR for dynamic tables.
UPDATE STATISTICS every night.
It would be helpful if tempdb data is on dedicated RAID1. Even better if tempdb log is on dedicated RAID1.
Hard disk configuration:
http://www.sqlusa.com/bestpractices/configureharddisk/
Kalman Toth SQL 2008 GRAND SLAM
New Book: SQL Programming & Database Design Using Microsoft SQL Server 2012
Thanks.
I have all those indexes.
I am rebuilding all indexes right now, and i'll do a update statistics. and try my query after that
to see if i goes faster.
k.r.
-
Wednesday, December 12, 2012 10:55 PMIt's not reading the data tha's slow; it's waiting for the disk to spin underneath the heads. The RPM for a disk drive is essentially the same as for a high performance car; 7000 or so. If the data is badly fragmented, every block-read wiats for the disk to spin 1/2 a rotation.
"Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
We are here to learn, to share knowledge, and to earn points; all in about equal measure.
-
Thursday, December 13, 2012 2:33 PM
It's not reading the data tha's slow; it's waiting for the disk to spin underneath the heads. The RPM for a disk drive is essentially the same as for a high performance car; 7000 or so. If the data is badly fragmented, every block-read wiats for the disk to spin 1/2 a rotation.
"Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
We are here to learn, to share knowledge, and to earn points; all in about equal measure.
Well, i am astonish ! It looks like you were right!
I have rebuild the indexes as told, and now i am having faster speed.
I would have never thought that an HDD could be so slow !
Thanks everyone, to help me undestand the slowlyness of HDD.
Even when building a cube, i got faster MB/sec then the 3 MB yesterday, it got as high as 35MB, i even saw a 150 MB/sec peek while doing the same query while a cube was processing.
Is there other thing i should do to defragment the disk ? defragment the tables?
Now i will rebuild index once a week, update stats each day.
Is there something else i could do as well for SAS ?
k.r.
.png)
