SQL Table Index Question

Question

SQL Table Index Question

mjbkm

Ten Centuries

Points: 1104
More actions
September 28, 2011 at 12:15 pm

#244602

OK - Now I am really confused and need help understanding this. Uggg.
I have a query that was running really slow with a huge table. The indexes were rebuilt and my query was going really quick. It was great. Then I had to delete / append a ton of records that took a while. Now the query is back to going slow again. I know I am missing something. Would this cause a speed issue again?
Also, what is the SQL Log file and would this cause any issues?
Thank you again!

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

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

bkubicek SSChampion Points: 10948 More actions · Answer 1

mjbkm (9/28/2011)
OK - Now I am really confused and need help understanding this. Uggg.
I have a query that was running really slow with a huge table. The indexes were rebuilt and my query was going really quick. It was great. Then I had to delete / append a ton of records that took a while. Now the query is back to going slow again. I know I am missing something. Would this cause a speed issue again?
Also, what is the SQL Log file and would this cause any issues?
Thank you again!

Your problem might just be that you need to update the statistics so the index will get used again.

It is also possible the inserts are not actually going to the end of the table, but are being inserted all over the table, then the index is in fact fragmented and needs to be rebuilt.

I would suggest running sp_updatestats first, if it is a statistics problem this should fix it.

If your tables clustered index happens to be a varchar or a guid, it is possible that you are fragmenting your table and index by inserting new rows. In this case you would need to rebuild the indexes again for the table.

mjbkm Ten Centuries Points: 1104 More actions · Answer 2

mjbkm

Ten Centuries

Points: 1104

September 28, 2011 at 12:32 pm

#1388001

How do I run sp_updatestats?

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

From a query window (connected to that database)

EXEC sp_updatestats

Or, maybe better

UPDATE STATISTICS <table name> WITH FULLSCAN

where <table name> is the name of the table that you did all those deletes/inserts into.

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

mjbkm Ten Centuries Points: 1104 More actions · Answer 4

Also - you said:

"It is also possible the inserts are not actually going to the end of the table, but are being inserted all over the table, then the index is in fact fragmented and needs to be rebuilt."

Is it possible to prevent this?

mjbkm Ten Centuries Points: 1104 More actions · Answer 5

GilaMonster (9/28/2011)
From a query window (connected to that database)
EXEC sp_updatestats
Or, maybe better
UPDATE STATISTICS <table name> WITH FULLSCAN
where <table name> is the name of the table that you did all those deletes/inserts into.

----------------- Is it ok to run this any time? I mean will it have any effect on any other database or table? What exactly does this do?

vince_sql Right there with Babe Points: 722 More actions · Answer 6

Sounds like the fill factor is high so it becomes fragmented quickly when you do insert, updates and deletes.

Have you tried lowering the 'fill factor'?

http://sqlvince.blogspot.com/[/url]

bkubicek SSChampion Points: 10948 More actions · Answer 7

mjbkm (9/28/2011)
Also - you said:
"It is also possible the inserts are not actually going to the end of the table, but are being inserted all over the table, then the index is in fact fragmented and needs to be rebuilt."
Is it possible to prevent this?

The question is how was your table designed. If it has a Clustered index that is an Int, or BigInt, or Numeric or something like that along with it being an Identity column you would not have this problem. Each time a new record is inserted it auto picks the next number (Identity), so the record would be inserted at the end of the table. If your clustered index happens to be some sort of varchar or a GUID then it is possible that inserts would not go to the end of the table. A way to prevent this would be to choose a different column for the clustered index once that auto increments. There are other possible solutions as well.

mjbkm Ten Centuries Points: 1104 More actions · Answer 8

vince_sql (9/28/2011)
Sounds like the fill factor is high so it becomes fragmented quickly when you do insert, updates and deletes.
Have you tried lowering the 'fill factor'?

OK - what is that? .... I will learn this all yet.

mjbkm Ten Centuries Points: 1104 More actions · Answer 9

bkubicek (9/28/2011)
mjbkm (9/28/2011)
Also - you said:
"It is also possible the inserts are not actually going to the end of the table, but are being inserted all over the table, then the index is in fact fragmented and needs to be rebuilt."
Is it possible to prevent this?
The question is how was your table designed. If it has a Clustered index that is an Int, or BigInt, or Numeric or something like that along with it being an Identity column you would not have this problem. Each time a new record is inserted it auto picks the next number (Identity), so the record would be inserted at the end of the table. If your clustered index happens to be some sort of varchar or a GUID then it is possible that inserts would not go to the end of the table. A way to prevent this would be to choose a different column for the clustered index once that auto increments. There are other possible solutions as well.

------I think I am good with this. My Clustered index is an INT. And YES it an Identity column. So it would be picking the next number / identity.

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

mjbkm (9/28/2011)
GilaMonster (9/28/2011)
From a query window (connected to that database)
EXEC sp_updatestats
Or, maybe better
UPDATE STATISTICS <table name> WITH FULLSCAN
where <table name> is the name of the table that you did all those deletes/inserts into.
----------------- Is it ok to run this any time? I mean will it have any effect on any other database or table? What exactly does this do?

http://msdn.microsoft.com/en-us/library/ms187348.aspx

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

vince_sql Right there with Babe Points: 722 More actions · Answer 11

mjbkm (9/28/2011)
vince_sql (9/28/2011)
Sounds like the fill factor is high so it becomes fragmented quickly when you do insert, updates and deletes.
Have you tried lowering the 'fill factor'?
OK - what is that? .... I will learn this all yet.

Im not saying this is definitely the problem, but it could be.

Please watch these videos, they will explain in more detail:

http://www.youtube.com/watch?v=cskh4s5lvyo (fill factor explanation)

http://www.youtube.com/watch?v=hAZnxZlVg_Q (how to tune/find out what needs tuning)

http://sqlvince.blogspot.com/[/url]

mjbkm Ten Centuries Points: 1104 More actions · Answer 12

GilaMonster (9/28/2011)
mjbkm (9/28/2011)
GilaMonster (9/28/2011)
From a query window (connected to that database)
EXEC sp_updatestats
Or, maybe better
UPDATE STATISTICS <table name> WITH FULLSCAN
where <table name> is the name of the table that you did all those deletes/inserts into.
----------------- Is it ok to run this any time? I mean will it have any effect on any other database or table? What exactly does this do?
http://msdn.microsoft.com/en-us/library/ms187348.aspx

----------------------Thank you. I will check it out!

mjbkm Ten Centuries Points: 1104 More actions · Answer 13

vince_sql (9/28/2011)
mjbkm (9/28/2011)
vince_sql (9/28/2011)
Sounds like the fill factor is high so it becomes fragmented quickly when you do insert, updates and deletes.
Have you tried lowering the 'fill factor'?
OK - what is that? .... I will learn this all yet.
Im not saying this is definitely the problem, but it could be.
Please watch these videos, they will explain in more detail:
http://www.youtube.com/watch?v=cskh4s5lvyo (fill factor explanation)
http://www.youtube.com/watch?v=hAZnxZlVg_Q (how to tune/find out what needs tuning)

----------------------Thank you. I will check it out!

mjbkm Ten Centuries Points: 1104 More actions · Answer 14

OK - I've gotten a lot of advice on this. Thank you so much. I think... the problem is solved. What was done -

1) Indexes rebuilt and additional added with the optimizer (update stats didn't seem to help)

2) And.... I think this was my HUGE problem. I started getting this error message in SQL testing even the most basic query:

"There is insufficient system memory in resource pool "internal" to run this query."

And

Error Source: .Net SQLClient Data Provider

Error Message: There is insuffient memory available in the buffer pool

Our SQL admin did something and even my largest query started running fast again. So I think I am back on track again.

Is anyone familiar with this error? Doesn't sound anything to do with my query. Sounds like problem with the server.

Thanks again!!