|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL performance problemI have a table that doesn't perform. The table gets 200-300 new records a
day. The table gets cleaned up and trimmed down once a month. Now I can't even do select count(*) from the enterprise manager for this table, which results in a timeout error. If I copy the whole table to the development database, then the table will perform just fine. I am running SQL 7.0 service pack 4 on Windows 2003 server. Would a dump and reload of this table do anything? Hi
Run sp_who2 and see what is going on as there might be blocking on the table. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Arne" wrote: > I have a table that doesn't perform. The table gets 200-300 new records a > day. The table gets cleaned up and trimmed down once a month. Now I can't > even do select count(*) from the enterprise manager for this table, which > results in a timeout error. If I copy the whole table to the development > database, then the table will perform just fine. > I am running SQL 7.0 service pack 4 on Windows 2003 server. > Would a dump and reload of this table do anything? Mike,
I ran SP_who2 but I didn't find any useful informatino. Arne Show quote "Mike Epprecht (SQL MVP)" wrote: > Hi > > Run sp_who2 and see what is going on as there might be blocking on the table. > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > > > "Arne" wrote: > > > I have a table that doesn't perform. The table gets 200-300 new records a > > day. The table gets cleaned up and trimmed down once a month. Now I can't > > even do select count(*) from the enterprise manager for this table, which > > results in a timeout error. If I copy the whole table to the development > > database, then the table will perform just fine. > > I am running SQL 7.0 service pack 4 on Windows 2003 server. > > Would a dump and reload of this table do anything? Have you tried running DBCC CHECKDB lately?
-- Show quoteAndrew J. Kelly SQL MVP "Arne" <A***@discussions.microsoft.com> wrote in message news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... >I have a table that doesn't perform. The table gets 200-300 new records a > day. The table gets cleaned up and trimmed down once a month. Now I can't > even do select count(*) from the enterprise manager for this table, which > results in a timeout error. If I copy the whole table to the development > database, then the table will perform just fine. > I am running SQL 7.0 service pack 4 on Windows 2003 server. > Would a dump and reload of this table do anything? I just did and I got
CHECKDB found 0 allocation errors and 0 consistency errors in database 'b2b' and There are 9291 rows in 136 pages for object 'Returns'. for the table that I have problems with. Show quote "Andrew J. Kelly" wrote: > Have you tried running DBCC CHECKDB lately? > > -- > Andrew J. Kelly SQL MVP > > > "Arne" <A***@discussions.microsoft.com> wrote in message > news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... > >I have a table that doesn't perform. The table gets 200-300 new records a > > day. The table gets cleaned up and trimmed down once a month. Now I can't > > even do select count(*) from the enterprise manager for this table, which > > results in a timeout error. If I copy the whole table to the development > > database, then the table will perform just fine. > > I am running SQL 7.0 service pack 4 on Windows 2003 server. > > Would a dump and reload of this table do anything? > > > Hi
Are you doing maintenance on your database to defrag the indexes? John Show quote "Arne" wrote: > I just did and I got > CHECKDB found 0 allocation errors and 0 consistency errors in database 'b2b' > and > There are 9291 rows in 136 pages for object 'Returns'. > for the table that I have problems with. > > > "Andrew J. Kelly" wrote: > > > Have you tried running DBCC CHECKDB lately? > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Arne" <A***@discussions.microsoft.com> wrote in message > > news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... > > >I have a table that doesn't perform. The table gets 200-300 new records a > > > day. The table gets cleaned up and trimmed down once a month. Now I can't > > > even do select count(*) from the enterprise manager for this table, which > > > results in a timeout error. If I copy the whole table to the development > > > database, then the table will perform just fine. > > > I am running SQL 7.0 service pack 4 on Windows 2003 server. > > > Would a dump and reload of this table do anything? > > > > > > John Bekk
I would as soon as I find out how. Arne. Show quote "John Bell" wrote: > Hi > > Are you doing maintenance on your database to defrag the indexes? > > John > > "Arne" wrote: > > > I just did and I got > > CHECKDB found 0 allocation errors and 0 consistency errors in database 'b2b' > > and > > There are 9291 rows in 136 pages for object 'Returns'. > > for the table that I have problems with. > > > > > > "Andrew J. Kelly" wrote: > > > > > Have you tried running DBCC CHECKDB lately? > > > > > > -- > > > Andrew J. Kelly SQL MVP > > > > > > > > > "Arne" <A***@discussions.microsoft.com> wrote in message > > > news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... > > > >I have a table that doesn't perform. The table gets 200-300 new records a > > > > day. The table gets cleaned up and trimmed down once a month. Now I can't > > > > even do select count(*) from the enterprise manager for this table, which > > > > results in a timeout error. If I copy the whole table to the development > > > > database, then the table will perform just fine. > > > > I am running SQL 7.0 service pack 4 on Windows 2003 server. > > > > Would a dump and reload of this table do anything? > > > > > > > > > This is the best start, IMHO.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Arne" <A***@discussions.microsoft.com> wrote in message news:9CC1CC3B-61AB-4B50-A377-43A45A61CB35@microsoft.com... > John Bekk > I would as soon as I find out how. > Arne. > > "John Bell" wrote: > >> Hi >> >> Are you doing maintenance on your database to defrag the indexes? >> >> John >> >> "Arne" wrote: >> >> > I just did and I got >> > CHECKDB found 0 allocation errors and 0 consistency errors in database 'b2b' >> > and >> > There are 9291 rows in 136 pages for object 'Returns'. >> > for the table that I have problems with. >> > >> > >> > "Andrew J. Kelly" wrote: >> > >> > > Have you tried running DBCC CHECKDB lately? >> > > >> > > -- >> > > Andrew J. Kelly SQL MVP >> > > >> > > >> > > "Arne" <A***@discussions.microsoft.com> wrote in message >> > > news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... >> > > >I have a table that doesn't perform. The table gets 200-300 new records a >> > > > day. The table gets cleaned up and trimmed down once a month. Now I can't >> > > > even do select count(*) from the enterprise manager for this table, which >> > > > results in a timeout error. If I copy the whole table to the development >> > > > database, then the table will perform just fine. >> > > > I am running SQL 7.0 service pack 4 on Windows 2003 server. >> > > > Would a dump and reload of this table do anything? >> > > >> > > >> > > Hi
The short cut way is to right click the database and choose Maintenance Plan. This will give you a wizard that will create a Maintenance Plan and the jobs to run it. If you want more control then you can write your own using the commands listed in other posts in this thread. I suspect running DBCC DBREINDEX on your table would have probably fixed your problem. John Show quote "Arne" wrote: > John Bekk > I would as soon as I find out how. > Arne. > > "John Bell" wrote: > > > Hi > > > > Are you doing maintenance on your database to defrag the indexes? > > > > John > > > > "Arne" wrote: > > > > > I just did and I got > > > CHECKDB found 0 allocation errors and 0 consistency errors in database 'b2b' > > > and > > > There are 9291 rows in 136 pages for object 'Returns'. > > > for the table that I have problems with. > > > > > > > > > "Andrew J. Kelly" wrote: > > > > > > > Have you tried running DBCC CHECKDB lately? > > > > > > > > -- > > > > Andrew J. Kelly SQL MVP > > > > > > > > > > > > "Arne" <A***@discussions.microsoft.com> wrote in message > > > > news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... > > > > >I have a table that doesn't perform. The table gets 200-300 new records a > > > > > day. The table gets cleaned up and trimmed down once a month. Now I can't > > > > > even do select count(*) from the enterprise manager for this table, which > > > > > results in a timeout error. If I copy the whole table to the development > > > > > database, then the table will perform just fine. > > > > > I am running SQL 7.0 service pack 4 on Windows 2003 server. > > > > > Would a dump and reload of this table do anything? > > > > > > > > > > > > Is this something that suddenly occurred today or has it been a
persistent problem? Also, is this problem consistent every time you try to do a count(*) or just sporatic? The most likely suspect would be a process that is holding a table or page lock. A long running transaction (perhaps with a cursor) can hold a lock on a table. Within Query Analyzer, you can use sp_who2 and check the BlkBy column to determine if another SPID is blocking your SPID. If so, then DBCC INPUTBUFFER (spid of the blocking process) will return the last statement executed, which would probably be what is locking the table. Also, if your table has clustered index on a natural key, and records are not being inserted in sequential order by this key, then fragmentation can occur. Use DBCC SHOWCONTIG ('mytable') to determine the level of data or index fragmentation. If needed, DBCC INDEXDEFRAG can be used to resolve it, but you will need to investigate why your table is getting fragmented in the first place. Another check would be DBCC CHECKTABLE ('mytable') WITH ALL_ERRORMSGS to determine is there is perhaps corruption in the index pages. Show quote "Arne" <A***@discussions.microsoft.com> wrote in message news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... > I have a table that doesn't perform. The table gets 200-300 new records a > day. The table gets cleaned up and trimmed down once a month. Now I can't > even do select count(*) from the enterprise manager for this table, which > results in a timeout error. If I copy the whole table to the development > database, then the table will perform just fine. > I am running SQL 7.0 service pack 4 on Windows 2003 server. > Would a dump and reload of this table do anything? JT,
That is a lot of useful information. Thanks. I solved by problem by 1. Deleting the corresponding asp.net programs 2. Copying the table to a test database 3. Dropping the table 4. Reimporting the table. 5. Restoring my web programs. Now my table is healthy again. Show quote "JT" wrote: > Is this something that suddenly occurred today or has it been a > persistent problem? Also, is this problem consistent every time you try to > do a count(*) or just sporatic? The most likely suspect would be a process > that is holding a table or page lock. A long running transaction (perhaps > with a cursor) can hold a lock on a table. Within Query Analyzer, you can > use sp_who2 and check the BlkBy column to determine if another SPID is > blocking your SPID. If so, then DBCC INPUTBUFFER (spid of the blocking > process) will return the last statement executed, which would probably be > what is locking the table. > Also, if your table has clustered index on a natural key, and records > are not being inserted in sequential order by this key, then fragmentation > can occur. Use DBCC SHOWCONTIG ('mytable') to determine the level of data or > index fragmentation. If needed, DBCC INDEXDEFRAG can be used to resolve it, > but you will need to investigate why your table is getting fragmented in the > first place. Another check would be DBCC CHECKTABLE ('mytable') WITH > ALL_ERRORMSGS to determine is there is perhaps corruption in the index > pages. > > "Arne" <A***@discussions.microsoft.com> wrote in message > news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... > > I have a table that doesn't perform. The table gets 200-300 new records a > > day. The table gets cleaned up and trimmed down once a month. Now I can't > > even do select count(*) from the enterprise manager for this table, which > > results in a timeout error. If I copy the whole table to the development > > database, then the table will perform just fine. > > I am running SQL 7.0 service pack 4 on Windows 2003 server. > > Would a dump and reload of this table do anything? > > > Hopefully, it won't happen again. Dropping, re-creating, and importing the
table is one way to solve a data or index corruption problem, but next time first try running DBCC INDEXDEFRAG against the indexes, specifically the clustered index. You can also try stopping / restarting the IIS service rather than deleting and re-deploying the web applications. Show quote "Arne" <A***@discussions.microsoft.com> wrote in message news:814F2F86-D0FF-48EB-BCF9-1D79B2C8D06B@microsoft.com... > JT, > > That is a lot of useful information. Thanks. > I solved by problem by > 1. Deleting the corresponding asp.net programs > 2. Copying the table to a test database > 3. Dropping the table > 4. Reimporting the table. > 5. Restoring my web programs. > > Now my table is healthy again. > > > "JT" wrote: > > > Is this something that suddenly occurred today or has it been a > > persistent problem? Also, is this problem consistent every time you try to > > do a count(*) or just sporatic? The most likely suspect would be a process > > that is holding a table or page lock. A long running transaction (perhaps > > with a cursor) can hold a lock on a table. Within Query Analyzer, you can > > use sp_who2 and check the BlkBy column to determine if another SPID is > > blocking your SPID. If so, then DBCC INPUTBUFFER (spid of the blocking > > process) will return the last statement executed, which would probably be > > what is locking the table. > > Also, if your table has clustered index on a natural key, and records > > are not being inserted in sequential order by this key, then fragmentation > > can occur. Use DBCC SHOWCONTIG ('mytable') to determine the level of data or > > index fragmentation. If needed, DBCC INDEXDEFRAG can be used to resolve it, > > but you will need to investigate why your table is getting fragmented in the > > first place. Another check would be DBCC CHECKTABLE ('mytable') WITH > > ALL_ERRORMSGS to determine is there is perhaps corruption in the index > > pages. > > > > "Arne" <A***@discussions.microsoft.com> wrote in message > > news:0A681ECC-E8E9-4D41-B913-FAD83055E6B9@microsoft.com... > > > I have a table that doesn't perform. The table gets 200-300 new records a > > > day. The table gets cleaned up and trimmed down once a month. Now I can't > > > even do select count(*) from the enterprise manager for this table, which > > > results in a timeout error. If I copy the whole table to the development > > > database, then the table will perform just fine. > > > I am running SQL 7.0 service pack 4 on Windows 2003 server. > > > Would a dump and reload of this table do anything? > > > > > >  |
|||||||||||||||||||||||
Other interesting topics