| Author |
Topic |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
 Posted - 2009-03-16 : 09:49:41
|
| I have been fighting with a database on a new server that is responding very slowly to simple queries. select count(*) from coverage takes about 40 secs. There is a primary key (pk) on the table. The query plan isSelect <-- Compute Scalar <-- Stream Aggregate <-- Parallelism <-- Stream Aggregate <-- Clustered index scan (cost: 100%)I put a unique index on the primary key and got a new query planSelect <-- Compute Scalar <-- Stream Aggregate (26%) <-- index scan (cost: 74%)The query now takes less than a second. Why would this be, there was no fragmentation and the statistics werealready updated before the second index was added.Jim |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-16 : 11:34:28
|
| The unique constraint allows SQL server to choose a better query plan.. the optimizer now "knows" about the nature of data..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-16 : 12:15:43
|
| That doesn't sound right. (It could be but I'd me disaapointed in SQL SERVER if it was!)It is possible that your clustered index was broken in some way? Using the new index seems a lot faster.Stats were up to date and low fragmentation you said. did you try rebuilding the index anyway? if you drop the UNIQUE index do you see the performance hit again?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-03-16 : 12:28:46
|
| The original clustered index was on the primary key, which I had already rebuilt and updated statistics (this was done as part of my earlier thread "New Server Slow"). When I drop the new index, the query goes back to taking 40 seconds. The pk is defined as[pk] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULLandPRIMARY KEY CLUSTERED ( [pk] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]I put my new index on this same column with CREATE UNIQUE INDEX and that's when things got better.Thanks,Jim |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-03-16 : 14:21:38
|
| "The unique constraint allows SQL server to choose a better query plan..the optimizer now "knows" about the nature of data.." --This doesn't sound right.It already had Primary key and SQL Server optimizer couldn't figure out nature of data. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-03-16 : 14:26:45
|
| Jim, What is the build of SQL Server and Windows you're running. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-03-16 : 14:33:59
|
| Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-03-16 : 14:57:31
|
| OK. good you're running SP3. I'm surprised why optimizer is chosing a parallel plan. Try Maxdop (1) in your query to force SQL server to run it serially. Again this is weird, but I want to see how long it takes with Maxdop 1 |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-03-16 : 18:00:36
|
| I tried with the maxdop option (and without the new index) and got the same results - about 40 secsJim |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-16 : 23:13:00
|
| how many rows does your table have? sometimes if the table is too small SQL Server can do a scan instead..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2009-03-17 : 02:41:02
|
| The clustered index actually is the data itself. A clustered index forces the data to be physically ordered the same way that the index is.When you do a Count(*), all you really need to know is how many rows there are... An index can store the inodes more densely than the data pages can store the entire rows, so there are often LOTS fewer pages in the index. Fewer pages means less I/O, so it happens quicker.When ever you will use your pk colomn in where clause, optimizer will use Clustered Index instead of Non Clustered index. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-03-17 : 07:38:22
|
quote:
how many rows does your table have? sometimes if the table is too small SQL Server can do a scan instead..
There are about 7.1 million rows. This query, on other servers, takes about 10 secs to run, and the tables just have the clustered primary key index.Jim |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-17 : 09:35:32
|
quote:
Originally posted by saurabhsrivastava
I'm surprised why optimizer is chosing a parallel plan.
It's scanning the whole table - often that is best done in parallel.I think lionzofdezert has got it right - can you return the results of SET STATISTICS IO for both indexes? BTW - the speed on different machines means little. IO reads & execution plans are usually the best measure for comparing physical operations. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-03-17 : 10:25:11
|
| [code]Table 'coverage'. with unique index on pk Scan count 1 , logical reads 6188 , physical reads 0 , read-ahead reads 0 , lob logical reads 0 , lob physical reads 0 , lob read-ahead reads 0.Table 'coverage'. With just pk clustered index(this is the primary key) Scan count 9 , logical reads 300095 , physical reads 151 , read-ahead reads 298544 , lob logical reads 0 , lob physical reads 0 , lob read-ahead reads 0[/code]Don't know what it all means, but there's definitely a lot more work in the second oneJim |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-17 : 11:13:21
|
The size of an index is the number of leaf level pages (as far as scans are concerned). The leaf level of the clustered index is the table - all the data. As such, there are typically less rows per page than a nonclustered index. In your case, your nonclustered index contains much less data (only key values) so there are more rows per page at the leaf level, so less pages to read.Logical reads are an excellent measure of a query's efficiency because it cannot be affected by hardware, load etc. The same data on the same pages modified by the same query = same number of logical reads.For the CI, logical reads: 300095If you were reading that many pages, wouldn't you launch a load of threads off in parallel too?  quote:
Originally posted by jimf
Table 'coverage'. with unique index on pk Scan count 1 , logical reads 6188 , physical reads 0 , read-ahead reads 0 , lob logical reads 0 , lob physical reads 0 , lob read-ahead reads 0.Table 'coverage'. With just pk clustered index(this is the primary key) Scan count 9 , logical reads 300095 , physical reads 151 , read-ahead reads 298544 , lob logical reads 0 , lob physical reads 0 , lob read-ahead reads 0 Don't know what it all means, but there's definitely a lot more work in the second oneJim
|
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-17 : 11:15:43
|
| Finally, if you don't need totally spot on results at all times, you can get this data from system tables even more efficiently. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-03-17 : 11:32:26
|
| Thanks Sir Flump, that makes a lot of sense. The fact that this simple query takes less than 1 sec on the prod server (with only the primary key clustered index) and 40 secs on the new server with the same index has got to be a hardware or configuration issue then.Thanks to everyone for all their help.Jim |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-17 : 11:40:45
|
quote:
Originally posted by jimf
that makes a lot of sense.
Well there's a first time for everything  What differences in results for execution plan and logical reads do you get when comparing the two servers? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-17 : 11:54:48
|
| If you are in 2005, sp_spaceused is usually up to date..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-03-17 : 12:30:52
|
Every day I learn something that someday else already knew! I don't have showplan permissions on the production server, so I can't compare to that. On the test server the table only has about 600,000 records, the query takes less than 1 sec, and produces the same query plans as my slow server. Other than hardware differences between my test and dev server(the slow one), I can see that the slow server has replication, but when I right-click properties on it I get an unable to connect error. Could this have anything to do with anything? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-17 : 14:41:13
|
There's no reason replication should affect things apart from the physical resources it uses. It should not be responsible for 40 times increasequote:
Originally posted by jimf
I don't have showplan permissions on the production server
Then this is the DBA's problem, not yours. Tell them to earn their money |
|
|
|
Next Page
|
Index and performance