My database keeps messing up
-
I have a client using my software which uses a SQL Server database. They are on a Windows 2000 Server, SP4. SQL 2000 SP3. There have been no software changes in the past 3 or 4 months at least, on the server. And my software hasn't changed in 18 months.
For no apparent reason, my database just stops working. My software times out when accessing the data and they have to stop and restart the SQL Server service to get it going again. This happens almost daily and has been a problem for about 10 days.
When my database is down, all the other databases in SQL are functional. I am able to go into Enterprise Manager and open any table for any database except for mine. When I click on the Tables icon to display a list of database tables for my database, Enterprise Manager hangs.
My assessment is there's definitely something damaged in the my database and would like some suggestions on maintenance that could be run to correct the issue.
Thanks for any help you can give.
-
Well, the first obvious steps would be to run sp_who (or sp_who2) from Query Analyzer, to find out about any running processes, locking issues etc. - and then DBCC commands like CHECKDB, SHOWCONTIG and possibly INDEXDEFRAG if the indexes are fragmented. Also some settings of the database can affect the behavior, if Autoshrink is ON or the growth increment is too small, it can cause terrible lags sometimes.
You didn't mention anything about recovery model, size of DB, number of users, what maintenance jobs are used and how often and so on, so it is hard to guess what might be the actual problem. At least for me, but I don't consider myself expert on similar problems - maybe other people will know better.
-
Thanks for your ideas. The client's shop is closed Thursday - Sunday for Thanksgiving so I will have lots of time to work on it. I am 2500 miles away from the client and I was under the impression that their server guy had a handle on it, but I didn't find any maintenance jobs. When I asked him if he'd run DBCC CHECKDB on the database in question, he said he was only vaguely familiar with that command. So DBCC commands have probably not been run in the 18 months it's been running my software. That will be the first order of business. Then setting up maintenance jobs. I've taken over the lead on supporting the SQL Server, so the BOL and I will become even closer friends over the weekend.
Thanks again........Cindy
-
No maintenance plan?? Does that mean that there has not been a backup of you DB in 18 months...Ouch! I agree with Vladan. The most common cause of EM hanging when trying to access your tables is user processes. What happens if you go to Management>>Current Activity>>Current Processes...Does it time out? Run sp_who2 as Vladan suggests. You've got to have some user process that is hogging up all of SQL Server's resources. It would make sense that you have to cycle SQL Server to get your DB up and running again because this would disconnect and kill the user process in question.
The real question is, if no new changes have been introduced into the application or DB, why is this process eating up so many resources? How much data has been added to your DB in the last 18 months? It may be that you need to defrag your DB or indexes. It may be that you need to revisit some of your SQL code or index choices. Performance tuning is more of an art than a science and many times, after an application has been up and running for some time, it is necessary to revisit your SQL code, indexing, storage, memory, and server choices.
-
John,
I hope it won't be that bad 🙂 I suppose they are backing up the DB regularly, just don't have a maintenance plan for it. It can be normal job (as we have it).
Well, Cindy, if the DBA does not know anything about DBCC commands then surely the first thing I would suggest is do FULL backup. Then create a maintenance plan (Optimization - reorganize data and index pages, with original amount of space; Integrity check - include indexes, attempt to repair minor problems), switch the DB to single user mode to make it run faster, and start the job with this plan. We have to run this maintenance approximately every 3 weeks to assure smooth operation... It is only a guess, but I suppose that the indexes and pages in your DB are in such a bad shape after all that time, that even relatively simple SQL eats up all resources. This maintenance can run quite a long time, even in single user mode, depending on the hardware and size of DB (ours is almost 200 GB and it takes like 12 hours).
When you are at it, you could also create a procedure that will check index fragmentation and run DBCC INDEXDEFRAG for all indexes where it is too high. Our DB has quite heavy traffic, so we run this procedure every other day; it can run without affecting the users too much, of course it is scheduled for night time when the traffic is at its lowest.
Good luck to you, and hope your product will get a better DBA next time :-).
PS: Before you have done the optimization and defragmentation, many queries will not use indexes at all, although they are available. Don't bother about it for the moment, check it afterwards. Only then it makes any sense to optimize queries.
-
Another thing that you want to look at is the indexing in general and not necessarily the maintenance plans (although they're useful!) ! We're going through this process at the moment with some of our DB's as these timeouts have started occuring due to the sheer number of records and locks have started occuring. Good things to double check :
1) The clustered index should be based on an increasing, sequential column. InsertedDate or an Identity Column being the ideal two. Potentially the worse field to have a Clustered index on is a UniqueIdentifier!
2) Superfluous Indexes. What i found on one of the Databases that i now look after is there was a compound index for columns a+b+c ... but then i found two more indexes, one for a, and one for c! These latter two arent required because the compound index covers them. The actual performance timings to delete 1000 records (one query!) from this table were orginally 25's ... but then droped by 8 seconds for EACH index! ... and i'm now getting a Valentine Card from the Disk Array!?
Basically, there's a good chance that you timeouts are occuring because Inserts and Deletes cant happen fast enough and these are blocking the table from other operations!
-
One minor comment on your superfluous indexes...
If you have an index on a,b,c then a second index on a is indeed a waste of space and time. The index on c however is useful. SQL only keeps statistics on the first column of an index. If you have a query that searches on c alone, SQL will not be able to use the index on a, b and c
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
BOL says this (see "composite index") :
"Covered queries can improve performance. Covered queries are queries where all the columns specified in the query are contained within the same index."
The example it gives is one the same lines as ur argument, but by the definition above, a query on column c is "covered" by this index! I didn't see any degredation of the queries when i removed my surplus indexes, so i'm with BOL on this one!!
-
BoL also says the following:
Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key. For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C. (Creating and administering databases->Indexes)
See the following example. The second query cannot use the index exen though the column is highly selective.
create table Test (
id int identity,
dt datetime,
rnd int
)
go
DECLARE @i INT
SET @i=0
WHILE @i<10000
BEGIN
INSERT INTO Test (dt,rnd)
VALUES (DATEADD(dd,-1*@i,GETDATE()), FLOOR(RAND()*1000))
SET @i=@i+1
END
GO
CREATE INDEX idx_Test ON
Test (dt, rnd)
GO
-- switch the execution plan on and run
SELECT dt, rnd FROM Test WHERE dt BETWEEN '2005/01/01' AND GETDATE() AND rnd=930
SELECT rnd FROM Test WHERE rnd=930
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Vinny,
unfortunately that is misunderstanding of the term "covered". What the BOL attempts to say in this part is, that if ALL columns specified in the query (from the table to which index belongs) are contained in the index, it can significantly improve performance. "Specified" = not only those in WHERE clause, but also all that appear in SELECT. Query is not covered by an index just because certain column - even if it is the only used column from the table - appears somewhere in some composite index. Query is covered, when all needed columns can be read from index, so that it isn't necessary to retrieve any data from the table itself.
-
Hey, guys, I worked on the database problem over the weekend, but am not very satisfied with the results.
My database was “hung” when I got onto the system on Friday. By this I mean that while in Enterprise Manager, I clicked my database, and then clicked "Tables" and EM got hung. All of the other databases were just fine. When I ended the task and went back into EM, I found that there were no users or locks in the database, which I would have expected.
I set up a Maintenance Plan to optimize the tables and indexes and to run a database integrity check. (A job for backing up the database was already in place.) Then I ran the jobs successfully. Question: I had the integrity check set up to "fix minor problems if possible" but the log from the job just shows that it ran successfully. If it had fixed something, would it report the fix in the log?
When I couldn’t get into my database, I also noticed that the SQL Server Agent was not running, which I thought was strange. After I stopped and restarted the SQL Server service, the SQL Server Agent was STILL not running, which I thought was really strange. So I started up the SQL Server Agent. Question, isn’t this supposed to start up automatically when the service starts?
This is a small database. The main table only has 46,000 rows and only about 20 rows are added per day. There are only 15 or so users. It really should not be giving me such a headache.
I have not heard from the client that they are having problems yet this morning, but I don't see that I did anything that would alleviate them.
-
The SQL Server agent does not have to be running for SQL Server to be up and functioning. Think of the agent service as a scheduler as it handles running your scheduled jobs. Many people prefer that this service is stopped during core business hours.
It seems strange that you are seeing such poor response from such a small database. Is your client sharing their SQL Server with other databases? Can you tell us more about the physical architecture that you are using?
-
The only other user database on this SQL Server is used by McAfee Enterprise Virus Scan. It appears as if that database was installed in May, 2005.
The server is a "small business server" and only has 40 GB of disk space.
While I was working over the weekend, I wanted to back up my database before I did anything. I wanted to make sure there was enough disk space on the server so I checked and found 3.91 GB of free space. When I looked in d:\Program Files\Microsoft SQL Server\MSSQL\Data, I found the database (.mdf file) using 145,792 KB and the transaction log named (.ldf) using 14,001,728 KB which is pretty huge.
So, in Query Analyzer I used the following statements to shrink the size of the transaction log: BACKUP LOG TILE WITH TRUNCATE_ONLY and then DBCC SHRINKDATABASE (TILE, TRUNCATEONLY).
Back in d:\Program Files\Microsoft SQL Server\MSSQL\Data, I found the database using 143,872 KB and the transaction log using only 1,024 KB which is good.
Now the d: drive on the server showed 17.2 GB of free space so that is good.
-
I don't see that you've run sp_who2 while your DB is hung. I would recommend that you do this the next time your DB is hung prior to bouncing SQL Server. I am not familiar with running McAfee on an enterprise level, but I would assume that this DB holds data pertinant to McAfee's activities enterprise wide. Depending on what is being stored here, whether it is collecting data from real-time virus scanning or scheduled scans, this DB could be quite busy especially during business hours. Again, run sp_who2 the next time your DB is hung. This will tell you about all user processes running on your SQL Server so even though you do not have users in your Application's DB, McAfee could be busy enough to cause resource contention problems on a small business server.
-
I will run sp_who2 next time. I didn't realize another database could have enough activity that it would cause problems with mine.
Thanks for your help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply