Speed up the Performance of SQL SERVER
-
Well I am glad that we both agree on that.
And I do agree with you on the memory usage.
Nowadays nobody thinks of the mem usage details. But I tried one a stored proc that created a variable 10000 times it was a varchar of 4000 char... While I actually needed a varchar of 137. After sizing the varchar correctly the sproc was 32 percent faster....... So yes I Agree with you completely...
But if you really have to use one server resource please let it be memory... ( if there is enough ) And if there is not enough it is the cheapest to buy
-
foll is SQLPERF(waitstats) REsults
At the end of Day.hope will give precise idea on actual situation.
Wait Type Requests Wait Time Signal Wait Time
-------------------------------- ------------------------ ------------------------ ------------------------
MISCELLANEOUS 1.0 0.0 0.0
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 0.0 0.0 0.0
LCK_M_S 1.0 266.0 0.0
LCK_M_U 0.0 0.0 0.0
LCK_M_X 8.0 1485.0 16.0
LCK_M_IS 0.0 0.0 0.0
LCK_M_IU 0.0 0.0 0.0
LCK_M_IX 0.0 0.0 0.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 29303.0 3.1483842E+7 3.0853648E+7
IO_COMPLETION 3786.0 22961.0 298.0
ASYNC_IO_COMPLETION 0.0 0.0 0.0
RESOURCE_SEMAPHORE 0.0 0.0 0.0
DTC 0.0 0.0 0.0
OLEDB 2880.0 1.6764564E+9 2576.0
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 44013.0 4.33784E+7 1.574131E+7
ASYNC_DISKPOOL_LOCK 0.0 0.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 0.0 0.0 0.0
PIPELINE_LOG 0.0 0.0 0.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 20418.0 161479.0 496.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 153.0 1795.0 0.0
XCB 0.0 0.0 0.0
DBTABLE 0.0 0.0 0.0
EC 0.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 29.0 0.0 0.0
CXPACKET 11736.0 326907.0 1142.0
PAGESUPP 374.0 47.0 32.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 0.0 0.0 0.0
CURSOR 0.0 0.0 0.0
EXECSYNC 0.0 0.0 0.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 0.0 0.0 0.0
LATCH_UP 3.0 0.0 0.0
LATCH_EX 14894.0 28669.0 1563.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 0.0 0.0 0.0
PAGELATCH_SH 6671.0 389.0 247.0
PAGELATCH_UP 186.0 32.0 16.0
PAGELATCH_EX 5695.0 938.0 142.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 8245.0 41343.0 141.0
PAGEIOLATCH_UP 17.0 313.0 0.0
PAGEIOLATCH_EX 102.0 1922.0 0.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 28346.0 165371.0 0.0
Total 176861.0 1.7520724E+9 4.6601628E+7
---------------------------------
DBCC SQLPERF(LRUSTATS)
=======================
Statistic Value
-------------------------------- ------------------------
Cache Hit Ratio 99.994644
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 447.0
MayurArt -
Mayuresh....
Have you looked in the profiler tro see if there are long running queries....
You can look everywhere but the basic information of where to look is provided by the profiler.
Look there first... Just let it run for a while and investigate the profiler.....
Look at the duration and you will see what the server is doing....And also have an indication why some things take so long....
-
"first one having Clustered index(Also PKey on 5 cols )and 2 other indexes on imp columns(i suppose)second table also Clustered index(PKey 6 cols) and 2 non-clustered indexes."
I think you should inspect your indexes, because long cluster key and short noncluster keys often is a bad idear. The fact that the default for a primary key is to cluster, does not make it best practice.
The primary key just needs to be unique. And the benefit of a clustered index is that data are IN the index and so an index on A,B,C,D,E,F is not much better than on A,B,C,D! Why is that?
Say each of above columns have 10 different values (selectivity = 0.1) then your cluster index selectivity is 0.1*0.1*0.l*0.1*0.1*0.1 = 0.000001.
Multiply this with 700.000 rows and you get 0.7. (Thats close enough to 1, which is your true key selectivity because your index is unique)
If you removed the F column from the cluster index you would get a shorter index key and 7 (actually 10) rows per index key.
Remove both E and F and you will get 70/100 rows per index key.
Say you can actually fit 70 rows per page, then all rows with a given value of A,B,C,D would all be on (at max) two pages.
Increasing the key with E will only sort the data on the page at the cost of a longer key. And thats not important because the server never reads less than a page and can subsort a page in memory in milliseconds. So you probably don't benefit at all from adding E to the index and certainly not F.
But all access to your data through the non clustered indexes need to go through your cluster index because the cluster key is the row locator in all other indexes.
Then a long cluster key will:
- First increase the cluster index size
- Secondly increase all non clustered indexes size
The larger the index the more I/O.
Knowing that the cluster key is actually included in all non cluster indexes allows you to do smart tuning.
Cluster on D,E,F
Non cluster on A,B,C
Is actually the same as:
Cluster on D,E,F
Non cluster on A,B,C,D,E,F
And now your cluster key is half the size and will reduce size of all other non clustered indexes.
So if any of your non cluster indexes are on the same columns as the cluster index, you should seriously consider making the primary key non clustered and make one of the non clustered index clustered.
regards
jensk
-
Forgot!
Another side effect of long cluster keys is that the risc that one of the columns in the index will be updated is higher.
And anytime you update a cluster key, the row may move to another page resulting in updates to all non clustered indexes.
(Granted, this is very unlikely a problem in this case, because the long key is in fact the primary key, which is rarely modified)
/jensk
-
Another consideration when using composite index is degree of uniqueness. You should anytime you use a composite index make sure the most to least unique item is the order of the columns. The reason is that only the first column is used to generate the statistics for a composite index.
In otherwords, if say you have a tinyint column in the index of which there are 4 values then it may look at this index poorly. But if there is a column in that index that is say a numeric(15,0) and had 1 million and you move it to the front you will cause a higher density stats value and thus a higher likelyhood that index will be used.
Going back to jensk said also, it is considered poor to use a composite key for a clustered index. It may still make a good Primary Key but if you need a composite PK then consider adding a INDENTITY column instead so you have a unique clustered key as well, but a smaller key value for the non-clustered indexes to find. The reason as jensk stated is the clustered index is used in every reference in a non-clustered index, so the overall size of the non-clustered index grows due to the width of the key values for the clustered index. By using a INDENITY value you ensure small width added to key and the highest uniqueness makes for smallest index movement when searching. If you use say another indexed column that is not totally unique the query engine will have to make one extra stop to compare the end results with the query requirements, thus slowing you down.
And a final thought here. On non-clustered indexes there are two ways to look at them.
First if you always query the same fields or a small subset from first column to last of an index then a composite is good for non-unique non-clustered indexes.
Consider all you queryies will generally use Col1, Col2 and Col3 and a few will use Col2 and Col3 and still a few others will use Col3 and Col1. Also, in this case Col3 is the most unique amoung them. Then an index on
Col3, Col1, Col2 is fine.
But if say Col1 is sometimes queried and Col2 at other and Col3 still others then you will be better with an Index for each. However if you query Col3 and Col2 and each has an index SQL Server may still use the index for both Col3 and Col2 to perform a hash match. This is called "index intersection" which SQL can automatically take advantage of, so each index in a Col2 and COl3 query still has advantage. But in the previous with Col3, COl1, Col2 index if you query Col2 it will more than likely have to perform and Index Scan to find Col2 values.
In otherword, make sure you Composite (non-unique) keys will lend to all your queries or break into individual indexes. But keep in mind it may make sense to only break off on and keep part of the Composite still.
Say if Col3 and Col1 are queried often, Col2 is queired often and Col1, Col3, and Col2 are queired in some cases. So Index on Col3, Col1 and and index on Col2 would be better.
-
What a load of cols...actually very useful, but tell me, have you ever seen a query plan with index intersection happening? I never did, are there any settings that need to be altered in order for it to happen, or design considerations for the tables they apply to? I did actually mention the clustered/non-clustered matter way back at the top, but I'm glad you've brought it all together.
Simon
-
quote:
What a load of cols...actually very useful, but tell me, have you ever seen a query plan with index intersection happening? I never did, are there any settings that need to be altered in order for it to happen, or design considerations for the tables they apply to? I did actually mention the clustered/non-clustered matter way back at the top, but I'm glad you've brought it all together.Simon
Sorry, I scanned most of the details and didn't catch you comment. So in fairness, insert in the last comment "To add to the statements of Simon..."
As for seeing index intersection. Unfortunately MS did not provide a icon or comment on actual intersection to be output in the execution plan (would be nice to see). Instead it is represented by a Hash Join. For a bit of info read "Understanding Hash Joins" in SQL BOL.
-
It sounds like there could possibly be a lot of disk contention occurring. We just set up our new data warehouse server with Raid 0+1 (mirrored then striped) instead of Raid 5 and we have seen a big performance increase. We also took our log files off the same drives as our databases. If you can change your disk strategy you may be able to get the desired speed up.
John
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply