CODEWITHSUNDEEP
sqlsql-serverdatabaseperformanceindexing
carbog
carbog

Reputation: 13

Very slow SQL queries on varchar column

I'll preface this question by stating that I am in no way a SQL expert.

However, I am trying to get a handle on what is happening with our database. We have been using a piece of code that would query a server database which has been working fine for years.

Recently though, we have been getting timeout errors. The database has grown over the years but it is still relatively small (the table in question has about 50 million records).

My suspicion is that the table has gotten too large for our non-professionally set up database to handle. So I tried to run a following simple query on the table SQL Management Studio:

SELECT * 
FROM [dbo].[Table] 
WHERE [Variable] = 'NNNNNNNN12'

The variable is set up as a varchar(50). This query took 30-60 seconds to complete. That seems like too much to me. We run a similar query on our software with the default timeout of 15 seconds.

Digging into a little bit, I realized that the table did not have a primary key designated. So I made the one but to my surprise it made it slower. The same query now takes about 5 seconds longer.

The next thing I tried was add an index for the varchar column but that again made it slower - adding another 10 seconds. Adding a full text index made it even worse with almost a minute response time.

The only thing I've managed to do was make it slower - so I need your help. Am I even on the right track here? Is 30 seconds reasonable for this type of query? Any other ideas on what I can try?

Upvotes: 1

Views: 8362

Answers (3)

John D
John D

Reputation: 1637

It's good you solved the performance problem with index include. If you find the performance problems creeping back, you can try reorganising or rebuilding the indexes.

I had a table of about 10 million rows with clustered index and over time Sql Server 2005 (and 2008 after we upgraded) kept reverting back to using it, even when I created an index as above which included the main columns involved in the SELECT. Initially all was good, the execution plan was using the new index, but after a few weeks our queries would start timing out again and we'd find Sql Server was using clustered index scan again.

We never got to the bottom of why it happened but a reliable workaround was to rebuild the indexes and update the statistics.

ALTER INDEX ALL ON dbo.[Table] REBUILD
UPDATE STATISTICS dbo.[Table]

For us, this was quick - 5 to 10 minutes - but try it on a copy of your production database first to see how long it takes in your case.

Over time, indexes can become fragmented. They start out with the pages corresponding to consecutive keys being physically together and each page is filled up to the FILLFACTOR setting. With inserts or deletes, there may be page splits. Logically related pages are no longer physically together and instead of one page at 100% you have 2 pages at 50% usage. You're jumping around more on the disk to retrieve data and there are more half empty pages to load.

You can check fragmentation with 

declare @db_id int = db_id('stackoverflow'); 
select db_name(database_id), object_name(object_id), *
from sys.dm_db_index_physical_stats ( @db_id, null, NULL, NULL, 'DETAILED' )

The column avg_fragmentation_in_percent should be as close to zero as possible for best performance. avg_page_space_used_in_percent is the average percentage of available data storage space used in all pages and should be high.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

For this query:

Select * FROM [dbo].[Table] where [Variable] = 'NN12345'

You need an index:

create index idx_table_variable on table(variable);

Then, there are a few cautions. The variable and constant value need to be of the same type and collation. So, if you have an index and the above query does not use the index, something else might be going on (such as collation incompatibilities between the table column and the default for the server or database).

Upvotes: 0

jthalliens
jthalliens

Reputation: 544

Take a look to the following scripts for some guidance..

USE YourDatabase
GO
SET STATISTICS IO, TIME ON;
GO
-- A table must have a primary key which implicity will create the clustered index
CREATE CLUSTERED INDEX CLIX_[Table]_YourPrimaryKey_Or_RowIdentifier ON [Table] (YourPrimaryKey_Or_RowIdentifier)
GO
-- A nonclustered index is needed if we are going to retrieve information using the Variable Column
CREATE NONCLUSTERED INDEX IX_Table_Variable ON [Table] (Variable)
GO
Select * FROM [dbo].[Table] where [Variable] = 'NN12345'
GO
--Check Results, copy the values from the messages tab to http://www.statisticsparser.com/
GO
Select [Variable] FROM [dbo].[Table] where [Variable] = 'NN12345'
GO
--Check Results, copy the values from the messages tab to http://www.statisticsparser.com/
--Compare the values for logical reads on the two executions
GO
--if you need more columns for the select statement, you can add them as included columns in this way:
GO
DROP INDEX IX_Table_Variable
GO
CREATE NONCLUSTERED INDEX IX_Table_Variable ON [Table] (Variable) INCLUDE (TheOthersColumns)
GO
--PLEASE make sure you are testing on the development environment prior to move to PROD.

Questions to be considered:

  • Do I need all the columns during the output result?
  • Any Varbinary or varchar(max) sql type during the execution? (slow performance if we have them included)
  • Can you add more filters in order to involve more indexes during the execution plan?

Upvotes: 0

Related Questions