Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2006-07-20 : 10:31:49
|
| I have a table on my OLTP server with a clustered index on id(varchar 11) and timestamp(numeric, 9,6 [for a total size of 18]).It is replicated to a reporting server, where the table structure is exactly the same.After a reindex and a fullscan for statistics, my OLTP server shows the average key length to be 18However, on my reporting server, the average key length is 31 (which shouldn't even be possible, 11+18 = 29). This is (I suspect) the cause of some radically different query execution plans for a some stored procedures. Instead of doing clustered index seeks, the SP on my reporting system does scans (of a 14GB table, it kinda takes a while). On my development server (Which is a clone via SAN of the OLTP server), the execution plan is all index seeks, and takes 20 minutes. On the reporting server (which is a much more powerful server, 8 processors, 14GB of RAM), it takes 1.5 hours, and often times out when called from the application.I've looked at table structure. I've looked at server configurations. I've looked at collation. I can't figure out why this is happening.Please, some pointers? I know I *could* solve the problem by dropping tables and recreating or doing a clone refresh, but those are drastic measures, and I wouldn't be able to tell what caused it in the first place. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-20 : 10:47:33
|
| Try the max datalength of the columns.Are you sure about the columns included in the index?No unicode?Is the index exactly the same?Are the datatypes the same as for other table columns it joins to - differing dtaatypes can cause it to not use an index.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
MuadDBA
628 Posts |
Posted - 2006-07-20 : 10:56:03
|
quote:
Originally posted by nr
Try the max datalength of the columns.Are you sure about the columns included in the index?No unicode?Is the index exactly the same?Are the datatypes the same as for other table columns it joins to - differing dtaatypes can cause it to not use an index.
I'll check the datalength. The columns included in the index are exactly the same....there's only 2, so I can be sure pretty easily.No unicode (ie nvarchar vs varchar), the tables are exactly the same size, rowcount, take up same space, etc.I'll check the datatypes as well, but I beleive they are the same. |
|
|
|
MuadDBA
628 Posts |
Posted - 2006-07-20 : 11:28:09
|
| Ok, nowhere in the data do I actually see a datalength > 11 for the varchar or > 18 for the numeric, which is to be expected. Max(datalength(id)+datalength(timestamp)) = 20, so it's something wacky with the statistics themselves. Is there any way to completely drop the stats for an index? I rebuilt the index completely last weekend using dbcc dbreindex, and that didn't change the statistics properly, so I'm kinda at a loss here. |
|
|
|
MuadDBA
628 Posts |
Posted - 2006-07-20 : 11:54:13
|
| Continuing on, I have also created my own statistics on the columns specified....brand new stats. They also show an average key length larger than what is possible. |
|
|
|
MuadDBA
628 Posts |
Posted - 2006-08-15 : 15:33:20
|
| So no one else has ever experienced this problem? Once I drop and re-create the index, the stats look just fine, but if I ever update statistics or create new statistics on my numeric column, they always at least double the key length. |
|
|
|
MuadDBA
628 Posts |
Posted - 2006-08-15 : 15:34:09
|
| Oh, I should mention this happens on the tables on the source data as well as the DBs where the tables are replicated to. |
|
|
|
|
|
|
|
|
Replicated tables with different statistics, help!