Indexing a Large Table

Question

Post reply

Indexing a Large Table

Rofty

SSCrazy

Points: 2585
More actions
February 1, 2010 at 2:10 am

#138653

I have the following table:
The table stores daily stock market prices for over 8000 instruments(stocks) per day
DateKey INT PK
InstrumentKey INT PK
Op FLOAT
Cl FLOAT
Hi FLOAT
Lo FLOAT
WAVG FLOAT
Vol FLOAT
Status CHAR(1)
Cap FLOAT
I use a composite key (DateKey,Instrumentkey) because one instrument can only have one record per day. it currently contains over 13,000,000 records
The problem I came across is that selecting data from this table takes over 5min, considering no one else is using the same database or server. Last week running a normal SELECT query with a basic WHERE clause, the SQL Server basically slowed down to a snails pace. Resulting in some users unable to connect to SQL Server.
The index on this table was created with the composite key and is set to clustered.
What can i possibly do so speed up the table and improve performance.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

Check fragmentaion on the table. It should improve the speed after attaining good fragmention.

Rofty SSCrazy Points: 2585 More actions · Answer 2

The Total Fragmentation is currently 15.2%.

I have to add that last week over 10,000,000 was added with SSIS package from 1500 csv files.

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 3

Yes..The table may be heavily fragmented. Perhaps you can Rebuild the clustered index offline, if you have nobody else using it.

There are quite a few threads in this forum which can provide info on rebuilding indexes..search and go thro them..

Regards,
Raj

http://Strictlysql.blogspot.com

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 4

/* SELECT query with a basic WHERE clause, the SQL Server basically slowed down to a snails pace. Resulting in some users unable to connect to SQL Server. */

Are u sure there was no other activity in SQL Server. If there is another heavy process eating up all the memory and CPU, then these issues can raise.

Also check the basic perfmon counters for CPU,Memory used,Disk quelength,

Lock wait,user Connections etc..

Also post ur system configuration.

Regards,
Raj

http://Strictlysql.blogspot.com

Rofty SSCrazy Points: 2585 More actions · Answer 5

SERVER SPECS:

HP ProLiant ML350

2x Intel Xeon 2.50GHZ processors

4GIG RAM

I have reorganized, rebuild the index and even after that it still shows no change.

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 6

hmm..I kind of guessed this when you said users cudnt connect..

Please check the following..(some of the stuff u wud have already checked..but anyways..)

> on System task manager check how much of CPU and memory SQL Server is consuming..

Is there any there process consuming CPU/memory... If nt wats overall usage and SQL'susage..

> Check the number of connections using sysprocesses. Also check how many are on runnable state.

how many are sleeping. USing last batch column check how long they have been sleeping.

> Check whether are there any queries that are blocking either using activity monitor on SSMS or using

query provided here :-http://strictlysql.blogspot.com/2009/07/check-currently-running-statements.html

> using perfmon check the values of the following counters.

Physical disk . avg disk queue length

Processor queue length

SQL Server Locks -> Average wait time

Buffer manager obj -> Buffer cache hit ratio

SQL Server memory mananger -> Total Server Memory

Target Server Memory

I believe u have set max number of connections ServerProperties ->connections ( default setting of 0 )..

Regards,
Raj

http://Strictlysql.blogspot.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please post the query, table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 8

first off, do as Gail says. It will help greatly in the event that your query isn't covered by an index. We'll be able to help then.

Also, 13 mil isn't that many records for an appropriate disk volume (SAN or other striped disk)

As far as users being locked out, it's probably a combination of the isolation level you're choosing and the lack of an appropriate index that covers the columns in your query.

This spiffy article can explain Isolation levels and you can choose one that's best.

http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

Nothing performs better than read uncommitted, but understand what you're getting with read uncommitted.

Good luck with that part, and send your query and execution plans as Gail suggests.

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Shriji SSCommitted Points: 1881 More actions · Answer 9

roelofsleroux (2/1/2010)
I have the following table:
The table stores daily stock market prices for over 8000 instruments(stocks) per day
DateKey INT PK
InstrumentKey INT PK
Op FLOAT
Cl FLOAT
Hi FLOAT
Lo FLOAT
WAVG FLOAT
Vol FLOAT
Status CHAR(1)
Cap FLOAT
I use a composite key (DateKey,Instrumentkey) because one instrument can only have one record per day. it currently contains over 13,000,000 records
The problem I came across is that selecting data from this table takes over 5min, considering no one else is using the same database or server. Last week running a normal SELECT query with a basic WHERE clause, the SQL Server basically slowed down to a snails pace. Resulting in some users unable to connect to SQL Server.
The index on this table was created with the composite key and is set to clustered.
What can i possibly do so speed up the table and improve performance.

Can you confirm that where clause does NOT have any other columns but DateKey and InstrumentKey ?

S t e f SSC Eights! Points: 977 More actions · Answer 10

You may also need to think about Statistics, i.e. with such a large data load you should set a job to update statistics, also check to see if auto update statistics is enabled, this may have kicked off the extra SQL load you have seen.

This article describes more:

http://support.microsoft.com/kb/195565

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

GilaMonster (2/1/2010)
Please post the query, table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

This will probably be something fairly easy to fix but unless you can help us help you by doing what Gail suggests above, all we can do is make suggestions.

As SQLBot suggests, 13 million rows really isn't that big and most queries should be nearly instantaneous... we just can't help unless we can see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 12

@s-2 t e f

He has Rebuild the index which implies stats are updated.

Also, the Original poster claims that people cant login or access the server which implies the problem is not just a missing index on 13 million row table.

Regards,
Raj

http://Strictlysql.blogspot.com

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

I agree with that. That's why we want to see that slow query and the execution plan for it. We're just shooting in the dark until that happens.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krayknot SSCertifiable Points: 7998 More actions · Answer 14

check the query execution plan and based on that you will come to know the reason probably.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com