June 22, 2009 at 8:11 am
You said nothing about checking security. I still find SQL servers with a default blank password. Everybody who ever worked there has full access to the server.
Tom Groszko
June 22, 2009 at 8:19 am
A good list. The article states - assuming you're a developer and not a dba, so the tuning issues are more on a dba's plate.
What keeps many of us up at night is not having our heads around the db well enough to understand what's going on. Performance tuning while important is not mission-critical unless there are serious user complaints.
The concept of (potentially) obsolete elements is another issue. The need to delete them is rarely mission critical, deleting something that's still needed becomes that. Yes, there can be issues with available space (large tables no longer needed) and it's a pain to wade through a bunch of no longer needed SP's, etc. My recommendation is document as many of the objects as you can where used, etc. - may take weeks or months. Anything not obviously used can get the XXX prefix treatment (or copied to an archive location) for at least a year.
June 22, 2009 at 8:23 am
I would suggest verifying that the recovery model is in line with how the backups are being taken. All too often I have taken over a server that is set to the full recovery model, while there are no transaction log backups being made.
June 22, 2009 at 8:39 am
It's a good list, though like others, I think deletion is hard one.
I'm for renaming the objects for 13 months, give time for an end of year process to come, and then roll them off month by month after that. If no one's complained in that time period, it's likely not being used. Could it be? sure, but you can script the code, include it in a backup, or even leave it in a folder on the server. Not the world plan in trying to keep a clean system over time
June 22, 2009 at 8:46 am
I'd like to see this kind of article geared towards DBA's rather than developers. The Backups one would be a definite for both though.
June 22, 2009 at 8:47 am
As someone who's tried to restore a tape that would no longer sync to the drive, I say that a test restoration is not just "ideal" but a standard. You're not going to get away with blaming the predecessor 3 months from now.
June 22, 2009 at 9:03 am
tom.groszko (6/22/2009)
You said nothing about checking security. I still find SQL servers with a default blank password. Everybody who ever worked there has full access to the server.Tom Groszko
'Fixing' security is another of those things as complex and high-risk as dropping unused objects. I have seen clients embark on months-long projects to address changing the SA password or other similar issues and STILL wound up missing things.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 22, 2009 at 9:11 am
nicely presented article, but this is just outlined, I'll be looking for more deeply explained version in near future from you.
June 22, 2009 at 9:44 am
Almost all of these can be automated pretty reliably:
Run a query to get all tables without a clustered index (heap tables), all tables without a primary key, and all tables without any indexes.
Run a query to see all database objects without the Description extended properties.
Review all UDFs which are not deterministic
Review any SPs declared WITH RECOMPILE
Review any code which contains SELECT *
June 22, 2009 at 11:13 am
Sylvia,
It sounds like you have never worked in a shop (1) with data architects/modelers and (2) where have strict change control requirements are enforced.
If so, your problem areas would be much reduced. I would try to focus on (1) finding old SPs and Jobs that are obsolete but are never formally dropped and (2) getting the development staff to adopt a policy of not writing code with dynamic SQL. Pie in the sky, I know, but worth a try.
June 22, 2009 at 11:16 am
I agree on not droping objects. Once worked in a place where there were quarterly and yearly reports with their own sp's. The same place also had windows scheduled jobs on 'under the desk' computers that ran reports and jobs for people in other departments on an on-demand basis. We figured that we would have to run a trace for over a year and a half to get 98% of the sp's that were run and would still probably miss 2%. It was not a pretty set of databases to try and manage.
John.
June 22, 2009 at 12:39 pm
P Jones (6/22/2009)
Run the performance dashboard reports and have a look at long running queries and missing indexes.
Also, check for duplicate indexes. In most the projects I had found duplicate indexes as one of the major performance blockade on both application and server side.
SQL DBA.
June 22, 2009 at 12:44 pm
Thomas (6/22/2009)
In SQL Server, unfortunately, all stored procedures are effectively scoped public which makes determining obsolescence difficult. It would be nice if there were a simple way of scoping a procedure such that it could only be executed from another stored procedure and not from the outside.
I use /* -----------------------------------------------------------------------------
** This SP MUST be run as at least a SECOND-LEVEL SP
** -------------------------------------------------------------------------- */
IF @@NESTLEVEL < 2
BEGIN
SET @errorcode = 002
SET @ErrorMsg = N'Cannot invoke Product Search subordinate SP directly.'
SET @ENDIT = -1 -- Quit...
CONTINUE
END
I'd also suggest looking at index fragmentation - we recently "inherited" a poor-performing implementation of Community Server. This CS implementation quickly fragments threads, posts and sections and it turns out the .Net code that builds the queries is very poor. However, running Idera's Defrag Manager with an hourly automated defrag of the top 20 worst fragmented indexes has drastically increased even badly-written query performance. Yes, this is a stop-gap measure, but until one is familiar with the code and has time to rewrite it, Index Defrag pays huge dividends and keeps the site going at an acceptable rate. And as a Developer with DBA tasks dumped upon one, tools like this that really work are well worth their price in man-hours saved. (Not an Idera Employee or reseller - just a happy User :-))
June 22, 2009 at 1:42 pm
After backups,
check db integrity, index fragmentation, other regularly schedule maintenance.
June 22, 2009 at 1:42 pm
Thanks everyone for the great comments and ideas. I should have been more explicit with the cautions for dropping objects. Of course, you would not just drop things that you think aren't necessary, and then wait for complaints. The last time I did a major batch of object deletes, I archived off all the object definitions, and bcp'd out all the tables into files before dropping them. Renaming an object by appending a suffix like "_DropIfNotUsed" is certainly another good way to do this - just requires another step to actually delete them.
Viewing 15 posts - 16 through 30 (of 83 total)
You must be logged in to reply to this topic. Login to reply