Rebuild master db

I've recently had to rebuild the master database using the rebuild -m
utility. However, i've noticed some strange behaviours in my application
since in particular that a few errors are mssing from the sysmessages table.
What would be the preferred plan of action from here to get the system
tables back to a similar state before the rebuild ( I don't have a usable
backup of the master database i'm afraid)? Would it be to reapply the latest
SP?
I'm using SQL2k on a win2k box and prior to the rebuild of master, it was
patched up to SP3.
Thanks
RichYes, it is probable that a service pack adds rows to sysmessages. Do you
know the error number of the missing rows?
Another alternative is that you, or your application added those rows.
Error number < 50000 should mean they were added by SQL Server (service pack
is likely), > 50000 mean application.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rich" <richbrownesq@.hotmail.com> wrote in message
news:%23UwsY15pDHA.744@.tk2msftngp13.phx.gbl...
> I've recently had to rebuild the master database using the rebuild -m
> utility. However, i've noticed some strange behaviours in my application
> since in particular that a few errors are mssing from the sysmessages
table.
> What would be the preferred plan of action from here to get the system
> tables back to a similar state before the rebuild ( I don't have a usable
> backup of the master database i'm afraid)? Would it be to reapply the
latest
> SP?
> I'm using SQL2k on a win2k box and prior to the rebuild of master, it was
> patched up to SP3.
> Thanks
> Rich
>|||There's 25 missing errors, ids ranging from 1960 -> 21520 and I think most
of these are sql server errors.
Would you recommend reapplying the SP or could i just insert the missing
rows? The issue i have with this is that there could be other modifications
to the system databases that i'm unaware of and wouldn't be fixed without
the latest SP.
However, when i run select serverproperty('productlevel'), it tells me SP3
is installed. Do you know if this will prevent me installing it again?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OeoPM75pDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Yes, it is probable that a service pack adds rows to sysmessages. Do you
> know the error number of the missing rows?
> Another alternative is that you, or your application added those rows.
> Error number < 50000 should mean they were added by SQL Server (service
pack
> is likely), > 50000 mean application.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Rich" <richbrownesq@.hotmail.com> wrote in message
> news:%23UwsY15pDHA.744@.tk2msftngp13.phx.gbl...
> > I've recently had to rebuild the master database using the rebuild -m
> > utility. However, i've noticed some strange behaviours in my application
> > since in particular that a few errors are mssing from the sysmessages
> table.
> > What would be the preferred plan of action from here to get the system
> > tables back to a similar state before the rebuild ( I don't have a
usable
> > backup of the master database i'm afraid)? Would it be to reapply the
> latest
> > SP?
> >
> > I'm using SQL2k on a win2k box and prior to the rebuild of master, it
was
> > patched up to SP3.
> >
> > Thanks
> > Rich
> >
> >
>|||Yes, you should reapply the service pack. You are correct in other things
can be affected by the service pack, like bug fixes in system stored
procedures etc. The reason why you get sp3 is probably because the function
checks the .exe file and that didn't change because of your rebuild. I did a
search in the archives and came across below, amongst others (strange that I
did not find anything in BOL or KB that you need to reapply service pack).
http://tinyurl.com/ue4s
Or full URL:
http://groups.google.com/groups?q=rebuildm+%22service+pack%22+group:microsoft.public.sqlserver.*&hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.*&selm=4oheDoLQDHA.1724%40cpmsftngxa09.phx.gbl&rnum=5
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rich" <richbrownesq@.hotmail.com> wrote in message
news:%234QgaA6pDHA.1672@.TK2MSFTNGP09.phx.gbl...
> There's 25 missing errors, ids ranging from 1960 -> 21520 and I think most
> of these are sql server errors.
> Would you recommend reapplying the SP or could i just insert the missing
> rows? The issue i have with this is that there could be other
modifications
> to the system databases that i'm unaware of and wouldn't be fixed without
> the latest SP.
> However, when i run select serverproperty('productlevel'), it tells me SP3
> is installed. Do you know if this will prevent me installing it again?
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OeoPM75pDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > Yes, it is probable that a service pack adds rows to sysmessages. Do you
> > know the error number of the missing rows?
> >
> > Another alternative is that you, or your application added those rows.
> >
> > Error number < 50000 should mean they were added by SQL Server (service
> pack
> > is likely), > 50000 mean application.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Rich" <richbrownesq@.hotmail.com> wrote in message
> > news:%23UwsY15pDHA.744@.tk2msftngp13.phx.gbl...
> > > I've recently had to rebuild the master database using the rebuild -m
> > > utility. However, i've noticed some strange behaviours in my
application
> > > since in particular that a few errors are mssing from the sysmessages
> > table.
> > > What would be the preferred plan of action from here to get the system
> > > tables back to a similar state before the rebuild ( I don't have a
> usable
> > > backup of the master database i'm afraid)? Would it be to reapply the
> > latest
> > > SP?
> > >
> > > I'm using SQL2k on a win2k box and prior to the rebuild of master, it
> was
> > > patched up to SP3.
> > >
> > > Thanks
> > > Rich
> > >
> > >
> >
> >
>sql

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

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!
JonathanSounds 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.googlegroups.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

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 &
#91;Factor]
FROM KRAM_Splits.dbo.Data
WHERE [No] = 10230
ORDER BY [EXDT] ASC
Query #2:
SELECT [No], MAX([Symbol]) AS [Symbol], MAX([Company]) AS &#
91;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!
JonathanSounds 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.googlegroups.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 &
#91;Factor]
FROM KRAM_Splits.dbo.Data
WHERE [No] = 10230
ORDER BY [EXDT] ASC
Query #2:
SELECT [No], MAX([Symbol]) AS [Symbol], MAX([Company]) AS &#
91;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

Rebuild index issue - - strange

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:

Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned........................: 184867
- Extents Scanned.......................: 23203
- Extent Switches.......................: 23324
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..............: 11.13%
- Extent Scan Fragmentation ...............: 35.46%
- Avg. Bytes Free per Page................: 60.0
- Avg. Page Density (full)................: 99.26%

Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned........................: 303177
- Extents Scanned.......................: 37964
- Extent Switches.......................: 42579
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..............: 43.19%
- Extent Scan Fragmentation ...............: 24.78%
- Avg. Bytes Free per Page................: 75.1
- Avg. Page Density (full)................: 99.07%

Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***if you look at your database ID's, they are different. did you run
this on 2 different databases?

Raziq Shekha wrote:
> Hi Folks,
> SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
> the command :
> dbcc dbreindex ('tablename')
> go
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
>
> Second output is from after the reindex:
>
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
> - Extents Scanned.......................: 37964
> - Extent Switches.......................: 42579
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
> - Logical Scan Fragmentation ..............: 43.19%
> - Extent Scan Fragmentation ...............: 24.78%
> - Avg. Bytes Free per Page................: 75.1
> - Avg. Page Density (full)................: 99.07%
>
> Following are my concerns:
> The following numbers are all higher after reindex than before reindex:
> pages scanned, extent switches, logical scan fragmentation, avg bytes
> free per page, avg page density.
> scan density is lower after reindex than before reindex
> Seems to me that the numbers that are higher after reindex should be
> lower and numbers that are lower after reindex should be higher? I
> didn't specify the fill factor in the dbcc reindex command so it should
> have used the default fill factor. The fill factor has never been
> changed on this machine.
> Am I missing something?
> Thanks,
> Raziq.
> *** Sent via Developersdex http://www.developersdex.com ***

really strange performance problem

hey
I have a sql2000 server SP3 and I mgrate a databse from SQL7. I run teo basically equal select: he first one 2 seconds the second one 58 minutes.....

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,20)
not in (select substring(col018_dominio,1,20)
from iis_uu_diario_resume where substring(col018_dominio,1,20)
= substring(uu_resume_ses_dummy_dummy.dominio,1,20))
option (maxdop 1)

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,30)
not in (select substring(col018_dominio,1,30)
from iis_uu_diario_resume where substring(col018_dominio,1,30)
= substring(uu_resume_ses_dummy_dummy.dominio,1,30))
option (maxdop 1)

the only differencei s that the substring range: 20 to 30. Notice that the limit is not fixed. SOmetimes the jump in execution time happende when I change from 90 top 91......
really I dont' know. (Fields ara varchar(90) but it was the same with varchar(255). the PLAN are exactly the same. in the second case the CPU was 50% fror 58 minutes fixed.
thanks for all the help (really needed)essentialy, for every record in the "uu_resume_ses_dummy_dummy" table you are looking at every record in the "iis_uu_diario_resume" table Using only one processor.

Since you will be looking at every record you have the potential of being delayed by locks, index leaf splits and other traffic. What happens if you run these selects on a quiet system. I suspect the time diffrence is small.|||I was runnng these queries both in a "busy" server (4 cpu, 4Gb RAM) and on a really quiet server (2 CPU, 4GB RAM) with same timing. Quiet server means that basically % of CPU without that select was netween 0 and 5%|||forgot things.

1) same times without the option of processing in one CPU only
2) both table are index on the specific fields.

what you say is ok. problem is:why almost identical queries have such a big big big difference in execution time?

Really strange behaviour concerning stored procedures

I have an application which comprises a VB6 client/server with a SQL
server back end. All the queries that the application uses are in
stored procedures which are initially created and occasionally
recreated by the application itself. The application does this by
building a CREATE PROCEDURE... SQL statement and then executing this
via the execute method of an ADO connection.
Any stored procs that involve 'Activities' are quite database intensive
as they use a view which is a multi-union view from lots of tables.
However, until now, once these stored procedures are compiled they run
pretty fast (typically 1 or 2 seconds).
Here's the problem (which has just started happening): when the
application creates (or recreates) a stored procedure involving
Activities, the query now takes about 10 seconds (instead of the
previous 1 or 2 seconds). However (and this is the really strange
part) when I get the procedure definition SQL from the stored proc and
execute it in Query Analyzer to recreate the SP, it creates a stored
proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
This is consistent (to a point) - each time I rebuild a stored proc
through the application the SP executes slowly and each time I create
it via Query Analyzer it executes quickly. In addition to this (just
to complicate things further!) after I've been testing it repeatedly
for a while it sometimes starts behaving ok - i.e. the stored proc runs
fast all the time regardless of whether it is created via the
application or via Query Analyzer.
I have tried this on our development SQL server and on a local MSDE
instance and I have tried it with different copies of the database -
the problem occurs in all tests so it doesn't seem to be db or server
related. I've noticed that the execution plan differs depending on how
the stored proc is created so I guess this is what's causing the big
time difference but the question is why should it matter how the stored
proc is created? The data is unchanged between tests and the stored
procedure text is the same - the only thing that changes is how the SP
is created (i.e. my application or Query Analyzer).
I have been tearing my hair out on this - can anyone please offer a
suggestion that might assist?
IanRead up on the SQL Server "procedure cache" and see if this would play a
role in what you are observing. Using SQL Profiler, you can trace
SP:CacheMiss, SP:CacheHit and other cache related events to determine what
is going on behind the scenes when your SP is being created or executed.
<ian__@.hotmail.com> wrote in message
news:1116345172.130396.298070@.g44g2000cwa.googlegroups.com...
> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||Hi
Why do you need to re-create the stored procedures all the time? Usually
stored procedures are static code and you just change the values of the
parameters passed to them!
John
"ian__@.hotmail.com" wrote:

> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||Are them being created with the same schema or owner in both cases (app and
QA)?
AMB
"ian__@.hotmail.com" wrote:

> I have an application which comprises a VB6 client/server with a SQL
> server back end. All the queries that the application uses are in
> stored procedures which are initially created and occasionally
> recreated by the application itself. The application does this by
> building a CREATE PROCEDURE... SQL statement and then executing this
> via the execute method of an ADO connection.
> Any stored procs that involve 'Activities' are quite database intensive
> as they use a view which is a multi-union view from lots of tables.
> However, until now, once these stored procedures are compiled they run
> pretty fast (typically 1 or 2 seconds).
> Here's the problem (which has just started happening): when the
> application creates (or recreates) a stored procedure involving
> Activities, the query now takes about 10 seconds (instead of the
> previous 1 or 2 seconds). However (and this is the really strange
> part) when I get the procedure definition SQL from the stored proc and
> execute it in Query Analyzer to recreate the SP, it creates a stored
> proc that executes quickly (i.e. back to the 1 - 2 seconds of before).
> This is consistent (to a point) - each time I rebuild a stored proc
> through the application the SP executes slowly and each time I create
> it via Query Analyzer it executes quickly. In addition to this (just
> to complicate things further!) after I've been testing it repeatedly
> for a while it sometimes starts behaving ok - i.e. the stored proc runs
> fast all the time regardless of whether it is created via the
> application or via Query Analyzer.
> I have tried this on our development SQL server and on a local MSDE
> instance and I have tried it with different copies of the database -
> the problem occurs in all tests so it doesn't seem to be db or server
> related. I've noticed that the execution plan differs depending on how
> the stored proc is created so I guess this is what's causing the big
> time difference but the question is why should it matter how the stored
> proc is created? The data is unchanged between tests and the stored
> procedure text is the same - the only thing that changes is how the SP
> is created (i.e. my application or Query Analyzer).
> I have been tearing my hair out on this - can anyone please offer a
> suggestion that might assist?
> Ian
>|||>> All the queries that the application uses are in stored procedures
which are initially created and occasionally recreated by the
application itself. The application does this by building a CREATE
PROCEDURE... SQL statement and then executing this via the execute
method of an ADO connection. <<
So you are such a bad SQL programmer that a random front end user
should be able to re-arrange the database. How did you expect to have
any data integrity?
If you had followed basic software engineering principles, the stored
procedures would be written, controlled and executed in the database
and not by the front end. This has nothing to do with SQL. This is
the foundations of all programming.
You need to start over, get a book on basic software engineering and
re-write what you have. As a rule of thumb, when you have "a
multi-union view from lots of tables", you usually have serious schema
design flaws.
Read about procedure casches, too. That is why dynamic things vary in
speed.|||On 17 May 2005 08:52:52 -0700, ian__@.hotmail.com wrote:
(snip)
>I have been tearing my hair out on this - can anyone please offer a
>suggestion that might assist?
Hi Ian,
First, let me state that I fully agree with the doubts expressed by John
Bell and Joe Celko regarding your design. I also agree with the possible
causes brought forward by JT and Alejandro Mesa.
But another possible explanation is this: check out the settings for the
options SET QUOTED_IDENTIFIER and SET ANSI_NULLS when creating the
procedure from QA or when creating it from ADO. These settings are saved
with the procedure when it's created (or rather: they are encoded into
the execution plan). A different value for one or both of these options
can result in a different execution plan.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Firstly, thanks to those who responded in a constructive and courteous
manner to my question - there's always a danger when posting that
small-minded individuals are going to respond with a load of unhelpful
and ill-informed comments. I really don't know how people can post
spiteful criticism based on assumption!
For the record, the application is of an extremely sophisticated nature
and is designed to enable the end-users to create their own, very
complex and powerful queries using a comparatively simple user
interface. These queries are created as stored procedures as they do
not change often and are executed frequently. The application ensures
data integrity but perhaps the notion of such an advanced design is
beyond people like CELKO?
Ian|||Thanks and congrats to Hugo! ANSI_NULLS were on in QA and off in the
ADO connection. I have amended the application code to set on before
rebuilding the stored procedure and the problem is fixed - I'm very
happy!
The reason this only started happening was due to a recent patch where
ANSI_NULLS were set OFF - this was not explicitly set before that
patch.
CELKO, see what can happen when you try to be helpful?
Ian|||On 18 May 2005 01:08:36 -0700, ian__@.hotmail.com wrote:

>For the record, the application is of an extremely sophisticated nature
>and is designed to enable the end-users to create their own, very
>complex and powerful queries using a comparatively simple user
>interface. These queries are created as stored procedures as they do
>not change often and are executed frequently. The application ensures
>data integrity but perhaps the notion of such an advanced design is
>beyond people like CELKO?
Hi Ian,
Actually, I think that Joe Celko has seen enough designs like this, AND
the results from it to make him very wary of this design.
Of course, Joe only sees the cases that have gone wrong (you don't pay
his rates to review a database that appears to be working fine), and
your situation might well be an exception, but still...
If you're allowing end users to write queries, then how do you gaurd
against the risk of injection of bad code? What do you do to prevent
someone including "DELETE FROM Customers WHERE 1 = 1" or "SHUTDOWN WITH
NOWAIT" or "EXEC sp_addrolemember 'System Administrators', 'Jeff'"?
Also, how do you gaurd against queries that run for hours, bringing the
database to it's knees or holding locks for so long that all concurrency
is lost?
If your end users are all developers and can be trusted not to do silly,
stupid, or even malevolent things, then why don't you simply grant them
the rights to add stored procedures and views, or to execute ad-hoc
queries against the database?
If your end users don't fall into this category, then you should not
give them a way to do development work they're not qualifeid for.
As I said - your situation might well be the exception. Not all
situations where designs like this have been implemented have
experienced the unwanteed side effects. But many do. I do hope that
you'll take Joe Celko's warning to heart - and that you seriously
consider other options.
(Since the queries don't change often, I'd set up a change request
system where the end users write stored procedures, sent them to a
skilled DBA or developer for review, and the latter executed the CREATE
(or ALTER) PROCEDURE script if the query is okay, or proposes
improvements and discusses them with the submitter of the query.)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> The application ensures data integrity but perhaps the notion of
such an advanced design is beyond people like CELKO? <<
LOL!! Of course you have always and will for the entire life of the
database, hire only *perfect* programmers. In the thousands and
thousands of lines of code they will write over time, nobody will
forget any business rules. Not one single rule! Amazing.
All the application code will use *exactly* the same algorithms. Never
mind that different programming languages use different truncation,
rounding, MOD() functions, string comparisons and so forth. The
perfect programmers will change the compilers or write their own
functions exactly the same way.
All third party packages will follow all of our business rules. How
they are going to do this when those rules are spread over thousands
and thousands of lines of application code, I don't know. Perhaps you
can tell me.
And when -- not if -- one of these integrity rules changes, the perfect
programmers will instantly propagate the changes in thousands and
thousands of lines of application code. And they will verify these
changes instantly.
And finally only perfect programmers will get to use QA or other tools
that go directly to the database without application code.
Advanced design? This is a return to a very primitive file systems
architecture. Talk to an old COBOL Programmer. The redundancy and
total lack of data integrity in those file systems are some of the
reasons we moved to DBMS and finally to RDBMS. You have re-discovered
1950's style ADP!