[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql 2008 primary key, index performance

Posted on 2014-08-13
16
Medium Priority
?
44 Views
Last Modified: 2016-04-19
When I build an index that looks exactly like my primary key, select performance increases dramatically. Why is this?

The table has 5 primary key fields, A B C D and LineNum. I want all the records for a given A, B, C and D. My select includes WHERE A= aaa AND B=bbb AND C=ccc AND D=ddd, and has ORDER BY A, B, C, D. The table has 4.1 million records in it. A fill takes about 25 seconds. When I create an index that also has A B C D and LineNum, the fill takes about 2 seconds. An index with just A B C and D takes about 2 seconds. When I delete the index, the query time goes back to about 25 seconds.

Any ideas why adding an index that looks like the primary key speeds up the query?
0
Comment
Question by:MikeBroderick
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
ID: 40258930
I believe the primary key index is only used if all columns in the primary key index are used.

When you don't pass in LineNum it forces a scan.

By creating another index with just the 4 columns you have a non-clustered index which allows the data to come back quicker.
0
 

Author Comment

by:MikeBroderick
ID: 40258941
But the other index had 5 columns too.
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40258951
The order of the columns can matter too.  

http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys


Do you have them in the same order if you do a script index / script table as?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40258986
Note: I'm assuming the PK is also the clustering key for the table in this case.

It depends on the width of the table; that is, how many bytes are in each row of the main table.

SQL will do a seek for as many leading columns in the clustering key as it can.  But once it finds the first value, it will do a sequential scan of the table until it hits the last value.  If the table is very wide, many more pages will be need to be scanned when using the table itself versus using an nonclustered index, whose pages contain only the key values.
0
 

Author Comment

by:MikeBroderick
ID: 40259050
The fields are in the correct order.

The primary key is the clustering key. A B C and D are varchar 10; LineNum is int. There are 3 other columns in the table, packed 6, packed 6 and varchar 256.

The matching record set is small, less than 100.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40259134
Given that, the clustered index alone should work just fine, unless there's HUGE fragmentation in the main table.  Other than that, I can't imagine why a nonclustered index is so much better.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40259140
Can you check the execution plan for the original case and check which index it is using, if its using a different index,  try to forcing sql to use the CI

SELECT A,B,C,D
FROM yourTable c
WITH (INDEX(PK_abcd))
0
 

Author Comment

by:MikeBroderick
ID: 40259144
How do you check an execution plan?
0
 

Author Comment

by:MikeBroderick
ID: 40259148
I found execution plan documentation. Sorry. I will do it.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40259152
If you want to actually run the query, you can use:

SET STATISTICS XML ON

before running the query.

If you only want to gen a plan, and not execute the query, you can do this:

SET SHOWPLAN_XML ON
GO
--query code goes here
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40259159
I suggest also checking the table fragmentation:

SELECT *
FROM sys.dm_db_index_physical_stats ( DB_ID('your_database_name'), OBJECT_ID('your_table_name'), 1, NULL, 'LIMITED' )
0
 

Author Comment

by:MikeBroderick
ID: 40259221
The code running the query is a Windows .Net forms application. The select clause has parameters (A=@A) in it. I copied the info into the sql window as follows:

USE [myTable]
GO
SELECT * FROM BCMLines
WHERE (SysName='yyyy' AND LibName='SPOR40' AND FileName='BSOSRC' AND MbrName='BSOAX1')
 ORDER BY SysName, LibName, FileName, MbrName, LineNum
GO

The results came back immediately (as I would expect and the plan shows using the primary key's index
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40259244
issue connecting to the database perhaps?  Are you only observing the 25 seconds from .Net or from SQL server?
0
 

Author Comment

by:MikeBroderick
ID: 40259257
I do not think it is a connection issue. I can have the app up one screen and SQL mgt stdo on another. I can click an app button to test, add the index, click, remove the index, click. I think a connection problem would show slow times for all entries.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 40259343
Now after running the query in SSMS, did you try it from  the application ? if its faster then it could be parameter sniffing
0
 

Author Comment

by:MikeBroderick
ID: 40259377
Yes, it is much quicker now.

I tried other columns (the A B C and D) values are sorted in ListViews so I can easily try different areas of the table. They run fast as well. Parameter sniffing stores the values in A B C and D, so I doubt it is parameter sniffing, but something changed.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question