June 24, 2009 at 8:12 am
Hello!
I have a slow running view which takes about 14secs to produce 42k rows.
The query is running on 2005 standard with plenty of ram, CPU, IO ect
SELECT *
FROM table1 A
WHERE
A.IsArchivedTF = 0
AND
(
A.AppCanSearchTF = 1
OR
A.AppCanReportTF = 1
)
I have simplified the view to take out the joins and column names because the problem lies with the one table and the where clause I have listed.
The where clause columns are all integers and in the execution plan you can see a clustered index scan on table1's PK.
I have run the query on 3 different boxes and it's slow on everyone...also indexes and stats are up to date.
What kind of index would be best to improve this query? The tuning wizard came up with crazy covered index over lots of columns
June 24, 2009 at 8:57 am
Could you post the execution plan for the query?
How many rows are in the table?
Also, do you really need all the columns for the tabel (select *) it will be more efficent if you select only the columns needed.
June 24, 2009 at 9:42 am
Given that your search criteria all point to what are apparently bit type columns, you aren't going to get a nonclustered index to work without an index hint unless it's a covering index. I think that your best bet is to create the clustered index something like this:
create clustered index cx_Table1 on dbo.Table1
(
IsArchivedTF
)
If you can't change the clustered index you could try something like this:
create nonclustered index ix_Table1 on dbo.Table1
(
IsArchivedTF
)
include
(
AppCanSearchTF,
AppCanReportTF,
Here you have to include all the other columns you want returned
)
That should cut the cost about in half assuming a fairly even distribution of values in IsArchivedTF. Indexes on bit columns are never going to be super terrific though.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 24, 2009 at 10:08 am
Thanks for the replies
DcPeterson,
Yes thats the kind of thing I want to do, I wasn't sure whether to make the index across all the columns or use included columns. I would prefer to keep the clustered index on the PK because it is being used in other queries that require the records to be ORDERED by the PK.I will try your suggestion for the covered index and post back. 🙂
Steveb,
Table is about 200000 rows, I didn't bother with the execution plan as it only will tell you what I posted above about the clustered index scan which takes 89% of the query.
I removed the column names for the purpose of this post (as mentioned above), in the actual view there is no "SELECT *" 🙂
The columns in the where clause are integer columns because the possible values are -1,0 and 1. I am going to change them to tiny int but I dont think that will be a big win.
The table is quite wide with some columns that are not regulary returned, so I am going to consider normalising these off to another table, but as this is a live customer website it will take some time to redevelop views and stored procs.
I have a good few years DBA experience in SQL server but I have just started a new job where there is alot more index /query tuning required compared to my previous roles.
Thanks again for the replies 🙂
June 24, 2009 at 10:22 am
Use the included columns, even though they take up the same amount of space whether they are included or part of the key, the index becomes a bit more expensive to maintain (and probably prone to fragmentation) if they are part of the key.
Whether you "normalize" off infrequently used columns is a separate subject, but I will just say that the frequency of use has nothing to do with normalization. It also doesn't have anything to do with this index. If you don't need the data in those columns, just don't include them in the included columns list, and make sure you aren't using select * in the query. That will get you your performance gain (such as it is).
Keep in mind that you don't need to include the clustered index key column(s) in the included columns list because the clustered index key is automatically part of any nonclustered index you create on the table.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 24, 2009 at 8:00 pm
DCPeterson (6/24/2009)
Keep in mind that you don't need to include the clustered index key column(s) in the included columns list because the clustered index key is automatically part of any nonclustered index you create on the table.
Reading that reminded me of a subtlety concerning clustered index keys, uniquiefiers, and which parts of the clustering key end up in which levels of the non-clustered indexes - depending on whether it is a UNIQUE index or not, whether the cluster columns are explicitly part of the non-clustered index key or not...and so on
These subtle behavioural differences can have a very marked impact on performance in some cases.
The details are here on one of Kalen's great blog entries.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2009 at 8:33 am
A clustered index is ALWAYS unique. Sure you can make the clustered key columns non-unique, but in the background, SQL adds a four byte unique identifier to the key if the user defined key is not unique. Thus at the nonclustered leaf level the clustered index key will always be unique whether that is over the defined columns or by the defined columns plus the unique identifier. Some people have been confused by this and thought that the unique identifer BECOMES the real clustered index key. This is not the case, the unique identifier is ADDED TO the clustered index key.
I don't believe there is ever a situation where a nonclustered index will not contain the entire clustered index key at the leaf level. This is why the size of your clustered index keys can have a HUGE impact on the size of the nonclustered indexes and the database as a whole.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 9:03 am
I added the index and it hardly made any difference at all, would making the view an indexed view be worth looking at?
June 25, 2009 at 9:10 am
Take a look at the execution plan and see if the index is being utilized. If you need assistance, post the execution plan and I'll take a look at it.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 9:20 am
DCPeterson (6/25/2009)
A clustered index is ALWAYS unique.
Not true. By way of demonstration...
drop table t1
go
Create table t1
(
col1 integer)
go
create clustered index idxt1 on t1(col1)
go
insert into t1 values(1)
insert into t1 values(1)
SQLSlammer , Please post the details of the plan as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
June 25, 2009 at 9:22 am
It wasn't, it was still doing a clustered index scan on the primary key. I used a query hint after that and it did do a index seek but the performance was about 1 second better.
Here is the table design (note the 4096 varchar)
go
June 25, 2009 at 9:24 am
Dave, did you actually READ what I wrote?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 9:26 am
Hahahaha Dave Ballantyne!! hello
I know you! used to be a SQL Server DBA at a place where you worked, I remember a debacle about permissions we once had...
That was about 3-4 years ago 😀
June 25, 2009 at 9:35 am
Ok now i really read you post with you..
SqlSlammer: Small world, cant say i remember are specific permissions issue though :ermm:
June 25, 2009 at 9:40 am
Some jumped up new DBA decided that too many people had sysadmin permissions, so he started revoking permissions..including yours....ring any bells? hehe
So anyway back to matter in hand...
I really don't understand why this table is so slow, it's baffling me.
I can post the execution plan, but I already explained the clustered index scan and there isn't anything else going on in the plan (simple query).
If you still want me to post it... I will
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply