Rebuild indexes affects other databases?

Hi all,
I'm facing a very strange problem. I have two databases (in
particular) that are attached to SQL Server 2005 Express (say, #1 and
#2), and I have two queries (among others) that I use to access their
data. Now, after rebuilding all indexes in database #1, the query to
access that database seems to be very fast. Then, I rebuild the
indexes in database #2, but the query to access database #1 takes about
6 times longer. However, the query to access database #2 is now very
fast. Rebuilding database #1 speeds access to #1 at the expense of
slowing down database #2. Here are the queries I'm using:
Query #1:
SELECT [EXDT] AS [Date], [AMT] AS [Dividend], [SHR] AS [Factor]
FROM KRAM_Splits.dbo.Data
WHERE [No] = 10230
ORDER BY [EXDT] ASC
Query #2:
SELECT [No], MAX([Symbol]) AS [Symbol], MAX([Company]) AS [Company],
MAX([SICCD]) AS [SICCD], MIN([Start_Date]) AS [Start_Date],
MAX([End_Date]) AS [End_Date]
FROM CO_History.dbo.Nos
WHERE [Symbol] = 'INTC'
GROUP BY [No]
ORDER BY [End_Date] DESC
Rebuild Query:
USE [CO_History] /* Or [KRAM_Splits] */
DECLARE @.TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.TABLES
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @.TableName + ' table'
DBCC DBREINDEX (@.TableName, ' ', 0)
FETCH NEXT FROM cur_reindex INTO @.TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
Both query #1 and #2 are prefixed by:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Here are some timing statistics. I got these using SQL Server
Management Studio Express with client statistics enabled. The last
column is an average of the first five trials.
Time Statistics (Database #1 after rebuild of #1)
Client processing time 0 15 31 0 63 21.8
Total execution time 62 93 62 46 125 77.6
Wait time on server replies 62 78 31 46 62 55.8
Time Statistics (Database #1 after rebuild of #2)
Client processing time 15 0 0 0 16 6.2
Total execution time 296 390 421 390 359 371.2
Wait time on server replies 281 390 421 390 343 365
Time Statistics (Database #2 after rebuild of #2)
Client processing time 0 0 16 31 16 15.6667
Total execution time 78 46 62 62 78 62
Wait time on server replies 78 46 46 31 62 46.3333
Time Statistics (Database #1 after rebuild of #1)
Client processing time 16 31 0 16 0 12.6
Total execution time 78 46 62 109 62 71.4
Wait time on server replies 62 15 62 93 62 58.8
Time Statistics (Database #2 after rebuild of #1)
Client processing time 0 0 0 16 0 3.2
Total execution time 281 437 312 312 343 337
Wait time on server replies 281 437 312 296 343 333.8
What is going on here? Am I losing my mind!?
Thanks for any help!
Jonathan
Sounds like you don't have enough RAM. When you do a bunch of index
rebuilds, it's likely those pages stayed in memory long enough for you do
query the same DB and get good results. When you go to query the other DB,
it ages out those pages from memory and has to go to disk to get the new
ones. If you have lots of RAM, this can be mitigated.
To make it an apples to apples comparison try either of the following:
Method 1
1) run DBCC DROPCLEANBUFFERS before the index rebuild and run your query
2) rebuild the index
3) repeat step 1 and compare the results
This will show you how long it takes for both a fragmented and
non-fragmented index to get the data from the disk
Method 2
1) run your query a few of times, then take stats on the last one or two
2) rebuild your index
3) repeat 1 and compare the results
This will show you the performance when the data are cached.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<jonathanve@.gmail.com> wrote in message
news:1140653001.018380.251370@.z14g2000cwz.googlegr oups.com...
Hi all,
I'm facing a very strange problem. I have two databases (in
particular) that are attached to SQL Server 2005 Express (say, #1 and
#2), and I have two queries (among others) that I use to access their
data. Now, after rebuilding all indexes in database #1, the query to
access that database seems to be very fast. Then, I rebuild the
indexes in database #2, but the query to access database #1 takes about
6 times longer. However, the query to access database #2 is now very
fast. Rebuilding database #1 speeds access to #1 at the expense of
slowing down database #2. Here are the queries I'm using:
Query #1:
SELECT [EXDT] AS [Date], [AMT] AS [Dividend], [SHR] AS [Factor]
FROM KRAM_Splits.dbo.Data
WHERE [No] = 10230
ORDER BY [EXDT] ASC
Query #2:
SELECT [No], MAX([Symbol]) AS [Symbol], MAX([Company]) AS [Company],
MAX([SICCD]) AS [SICCD], MIN([Start_Date]) AS [Start_Date],
MAX([End_Date]) AS [End_Date]
FROM CO_History.dbo.Nos
WHERE [Symbol] = 'INTC'
GROUP BY [No]
ORDER BY [End_Date] DESC
Rebuild Query:
USE [CO_History] /* Or [KRAM_Splits] */
DECLARE @.TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.TABLES
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @.TableName + ' table'
DBCC DBREINDEX (@.TableName, ' ', 0)
FETCH NEXT FROM cur_reindex INTO @.TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
Both query #1 and #2 are prefixed by:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Here are some timing statistics. I got these using SQL Server
Management Studio Express with client statistics enabled. The last
column is an average of the first five trials.
Time Statistics (Database #1 after rebuild of #1)
Client processing time 0 15 31 0 63 21.8
Total execution time 62 93 62 46 125 77.6
Wait time on server replies 62 78 31 46 62 55.8
Time Statistics (Database #1 after rebuild of #2)
Client processing time 15 0 0 0 16 6.2
Total execution time 296 390 421 390 359 371.2
Wait time on server replies 281 390 421 390 343 365
Time Statistics (Database #2 after rebuild of #2)
Client processing time 0 0 16 31 16 15.6667
Total execution time 78 46 62 62 78 62
Wait time on server replies 78 46 46 31 62 46.3333
Time Statistics (Database #1 after rebuild of #1)
Client processing time 16 31 0 16 0 12.6
Total execution time 78 46 62 109 62 71.4
Wait time on server replies 62 15 62 93 62 58.8
Time Statistics (Database #2 after rebuild of #1)
Client processing time 0 0 0 16 0 3.2
Total execution time 281 437 312 312 343 337
Wait time on server replies 281 437 312 296 343 333.8
What is going on here? Am I losing my mind!?
Thanks for any help!
Jonathan
|||OK, I see what you mean. This computer has 512MB which is on the low
side.
But I'm unclear about one thing: After reindexing, the query seems to
take almost no time at all (~0 msec), which means that a good part is
in memory. But even without reindexing (like when I run the query on
database #2 after indexing database #1), wouldn't running the query a
few times put a lot of the pages from the query in memory so that it
would also take almost no time after that? It seems like even after
running the query a bunch of times, it still takes about 200 msec or
so, which is better than the first run (so it is caching something),
but not as fast as after a reindex. What's the reason behind this?
Also, is SQL Server 2005 a bit more memory hungry than MSDE (which is
what I used before)?
Thanks for the quick response!
Jonathan
sql