| Jerry Dausman's profileThere's an answer to you...BlogLists |  Tools  Help |
|
|
February 26 Forgotten DependanciesCorrect me if I'm wrong, but I believe past versions of SQL Server used to show all dependancies for all types of objects. Today I had to find out in which stored procedures a certain view was used. I ran sp_depends on the name of the view and all I got was the tables that the view depended upon. Knowing that the view was used in at least one stored procedure I proceeded to investigate the system table sysdepends. Lo and behold, there was no information for that stored procedures relationship to the view in sysdepends! What to do? Write my own procedure that I'll call spDependsAlso:
Of course, you may want to create this as a function instead. And it would certainly work faster if we used the 'CONTAINS' function in the WHERE clause ... but then we'd have to use Search Service and regularly update the search indexes. I find this version works well enough for me.
February 28 The New DBA: Periodic Tasks, Part TwoThis is the seventh and final post in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.
Periodic Tasks, Part Two
Task 19: Use Profiler to check for deadlocks and locking timeouts. See if a particular user or application is causing problems and investigate.
Deadlocks indicate data being either locked for too long, or being locked in the wrong order. In either case processing time suffers. Rewriting code is often indicated.
Task 20: Read! Books Online, SQL Server Magazine and other resources will give you a wealth of new ideas for improving your systems.
The boss won't mind if it wasn't your idea originally ... just as long as it gets the job done! And it can't hurt to be known as knowledgeable. (Go ahead and take credit if it was one of my ideas ... I don't mind!)
Task 21: Don't forget to ask your boss for a raise when your hard work pays off with increased throughput or decreased response time or more utilization of that ancient equipment they make you put up with!
It generally can't hurt to ask!
February 27 The New DBA: Periodic Tasks, Part OneThis is the sixth in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.
Periodic Tasks, Part One
Task 16: Restore your offsite (or off-machine) backups of all production databases to a test machine to prove your restore strategy. Run this drill a minimum of once a year to train for minimizing downtime and eliminate flaws in your restore process.
How do you know if your offsite storage provider has kept your tapes in good condition? How do you know if your second-in-charge can handle the restore process when you're on vacation? How do you know how long a restore will actually take ... if you don't actually test it?
Task 17: Rerun baseline traces at least twice a year to spot trends that a) indicate new problems, or b) may lead to processing bottlenecks.
Organizations tend either to grow or die, and growing organizations have growing data requirements. Sooner or later some problem or bottleneck will arise, and it's always wise to know when that day is coming. Plan for it.
Task 18: Talk to end users regularly to get a sense of: new developments, changing requirements, comfort level with the current system, etc.
This task (like the previous one) is focused on the future of the organization. It's always good to have a little prior notice of anything that will affect data useage in the organization. As an added benefit, you will be seen as someone approachable and knowledgeable in the organization. Alternatively, if your organizations data requirements seem to be shrinking ... you'll know when to polish up that resume!
Please add your comments ... and stay tuned ... there's more to come in this series!
February 22 The New DBA, Secondary Tasks, Part TwoThis is the fifth in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.
Secondary Tasks, Part Two
Task 12: Use sp_depends and the database table sysdepends to determine the number (and structure) of levels of views built on views. Try and limit the number of view levels.
A view that references a view treats the referenced view as a subquery. In other words, the subquery is optimized independantly of the first view. Since, ultimately, all our data comes from tables, it stands to reason that each view can (and should) directly reference tables so that only one optimization needs to be performed.
Task 13: Use sp_depends and sysdepends to determine the stored procedures that call other stored procedures, and how far they are nested. Determine the critical stored procedures in the nexting structure and analyze them to determine if a) we can reduce nesting levels, and/or b) if these critical stored procedures can be written to be more efficient.
Not only is optimization involved, but also nesting levels. SQL has a limit of 32 nesting levels, and we don't want to run into that limit!
Task 14: Become familiar with roles and permissions in each production database. Understand the particular internal security measures.
Follow the rule of thumb that users should only have access to the data they need to do their job. Figuring out what that data is, and the best way to lock out all the other data is the hard part. Design of these access rules requires a thorough knowledge of the business application and the environment in which it runs.
Task 15: Look for optimizer hints in SQL code. Determine why they are in place. See if you can take them out without compromising reporting or performance.
In my humble opinion "optimizer hints" are a misnomer. I believe they should be called "optimizer commands" because that's just what they are: directives to run the command the way the programmer wants even if the optimizer determines a "better" way. The optimizer almost always knows the best way to process data. There are situations where optimizer hints work better, but these are rare. You really need to know your database to out-think the optimizer.
Please add your comments ... and stay tuned ... there's more to come in this series!
February 08 The New DBA, Secondary Tasks, Part OneThis is the fourth in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.
Secondary Tasks, Part One Task 9: Run the Index Tuning Wizard. Instead of implementing the suggested changes ... have them scripted and review them. Consider how the new indexes might assist processing. Implement them only if they make sense according to the database structure and common queries. Only consider dropping indexes if they are not used for critical reporting (i.e., talk to the users first).
The Index Tuning Wizard is a wonderful tool, but its only a tool. You must always use it with some common sense applied.
Task 10: If you do implement index changes, use Profiler to retrace the processing period to verify improved performance. If performance is the same, try running your "average period" trace with the new index. If there's still no gain, consider undoing the change.
Why make the database structure more complex if you don't have to?
Task 11: Use Profiler to determine the 10 longest running stored procedures on each production database. Review the code of each of them to determine a) poor coding technique, or b) who needs training!
One or two of these 10 could be the straw that broke the camels back, if not today then tomorrow when processing loads get heavier. And it's also good to know who does good work and who doesn't. This will be your introduction to the level of quality in your database.
Please add your comments ... and stay tuned ... there's more to come in this series!
January 29 The New DBA: First Tasks, part TwoThis is the third in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.
First Tasks, continued:
Task 6: Determine if a 'Database Maintenance Plan' has been instituted for all production databases, as well as Master, Model and MSDB databases.
Hey, if you ain't got a plan to regularly test and backup the database ...
Task 7: Script the production databases and become familiar with their structure.
The sooner you know about the data and its structure, the better. Sooner or later someone will come with a business problem, and knowing the data will probably be key to finding a solution.
Task 8: Find out who the important user contacts are and introduce yourself.
The users are the ones who: input the data, require the data, or both. They also determine acceptable performance criteria. Keep in mind that the user contacts may be different for each production database. In any case, knowing the data, coupled with learning about the user's business problems, will help you anticipate requirements, bottlenecks and solutions.
Please add your comments ... and stay tuned ... there's more to come in this series!
January 27 The New DBA: First Tasks, part OneThis is the second in a series of posts concerning what a person, just hired as a new DBA, needs to keep in mind to hang onto that job.
First Tasks:
Task 1: Make sure you have good backups of all production databases.
Task 2: Make sure you have good backups of the system databases.
Task 3: Make sure you have good backups of all databases in development.
What do I mean by 'good'? I mean backups you can actually read and use when you get them back from offsite storage. Hey, if you haven't tested the whole system round-trip, how do you know it will work? If you don't test, you're just making an assumption. People get fired for bad assumptions.
Task 4: Find out which login mode the server(s) are set for: 'Windows Only' or 'Mixed' mode. If 'Windows Only' ... get rid of unused SQL logins. If 'Mixed', and you're daring, try changing the password on the 'sa' account.
It's nice to know whether your network administrator is the only one handling passwords, or if you will be called on to do so. And if you are not using SQL logins, why don't you tighten security and switch the server to 'Windows Only'? As for changing the 'sa' account password, if someone complains their program isn't working anymore ... that's good! Programs (and users) shouldn't be using a system administrator account ... they should have only logins that let them do just what they need and no more. Tighten up your security!
Task 5: Determine what level of software you are running (i.e., service packs and updates).
It will help you save some time if you do have bugs. In any case, you should consider upgrading to the latest versions, to minimize bugs you may encounter in the future.
More to come in this series ... stay tuned!
January 16 The New DBA: what to do when hired as.Several years ago, back in the good old days of SQL Server 7, a student asked me about what a new DBA should spend his/her time on. That began a class discussion that I have continued through all my SQL administration classes to the present day. In fact, the discussions have been 'codified' into a 2-page memo, of which I am keeper. In a series of posts I will be revealing to you, privileged reader, the contents of this memo, which is the combined work of many students, fellow MCDBAs and fellow MCTs.
The sections of the memo which will appear in subsequent posts are entitled: First Tasks, Later Tasks, and Periodic Tasks. Basically, the tasks are common sense items that every DBA should be doing. The list of tasks is probably not complete ... because one of the criteria for inclusion on the list (which I am now dropping) is that the list be no longer than two typewritten pages. The list is also in (roughly) order of priority. So please add your favorites or tell me where we have our priorities listed wrong.
September 01 Full Join Feathers Consultant's NestBackground
So I'm teaching the second day of MS2071 Querying SQL Server 2000 with Transact-SQL and I notice that the section on Outer Joins is missing the Full Join. Personally the FULL OUTER JOIN is a sentimental favorite of mine. In my previous life as a consultant I could almost always walk into a company of 150+ employees and find a problem to solve. One of my favorites was multiple employee files that don't match. For instance, say you have a Human Resources department and a Payroll department. One major company I used to work for would have employees listed in one that were not in the other. Well, payroll would find out within two weeks if they were missing someone ... HR might never find out.
SQL Server to the Rescue
To solve this problem I would set up a linked server in SQL Server. Let's say payroll was run from an application using SQL Server as the data store. That server would link to a server we'll call HR. The next step tells us all we want to know about employees in one system missing from the other ...
Of course, the first and last names have to be entered with the same spelling in both databases. In any case you'll get a listing of only those employees who aren't in both places ... or whose names are misspelled in one place.
Finally
This simple FULL JOIN made me a bit of money in my career, mainly by establishing me as a computer genius in the eyes of the client! And SQL Server makes it easy.
By the way, you can't do this in a .NET dataset ... because you can only relate two tables together through a parent-child relationship. Too bad! (For more info see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp.)
August 31 MOM Doesn't Like SQL TempDB!MOM Starts a Discussion in Class
Today a student in my SQL class showed me an error message he gets through MOM (Microsoft Operations Management). It's the 9002 Log File Full error message for TempDB database. He wants to know how to avoid getting the error message ... when he already has the log file set to expand with no maximum size limit.
One way might be to back up the log file to truncate out the completed transactions. Try running the following code:
Did it work for you? Of course not! SQL Server doesn't allow you to do backups on TempDB! (You know, I went through all the pages in Books Online referencing [a] TempDB and couldn't find anything on not being allowed to backup and [b] all the pages on BACKUP and couldn't find anything on not backing up TempDB! Learned something new today!)
A Possible Solution?
It turns out that Googling "TempDB backup" will get you a lot of information on this same 9002 error. Many of the sites tell you to do the same thing ...
Sounds reasonable ... you get rid of all the completed transactions and have an empty log file. What's wrong with this picture? Let me give you a 'what if' ... What if the TempDB database automatically gets rid of completed transactions? Then the above BACKUP statement doesn't do anything! What am I talking about? Try running the following statement ...
This will tell you whether the TempDB database truncates its log file on reaching a checkpoint. (In other words, everytime data is written to the data files it is automatically cleared from the log file.) The above statement will tell you that the 'Truncate Log On Checkpoint' option is 'ON' for your TempDB database. Why do I know it's on? Try changing the option by running the following ...
to turn off the option. You will get a nice error message telling you 'no way'.
In class we even went so far as to look into the SP_DBOPTION code to see if we could find where it prevents you from changing the option. It's not in the stored procedure! That means it must be in the SQL Server database application itself.
What's Your Problem?
So what's the real problem? And why does it happen? And what can we do about it?
The problem is that we seem to be filling up the log file. How can we get a 9002 'Log File Full' error message if we're always clearing it out? There are only two possibilities. Either we are getting a false 9002 error (highly improbable) or SQL Server is moving a large amount of data through TempDB as a single transaction. When we have the 'Truncate Log on Checkpoint' option set, or we run the 'Backup Log with Truncate_Only' statement, we are truncating only completed transactions. If the data transaction is large enough to fill the TempDB log file, it will generate the 9002 error all on its own. The second scenario seems to be the root of the problem.
How Do We Solve This Problem?
The simple answer, as one student suggested, is to ignore it! Well, as an MCT I feel I must follow the unwritten code of all SQL MCTs and find a better solution ...
Solution 1: Make the size of the TempDB log file bigger to start with. If anything it should decrease the frequency of this error popping up. But there may be a better way to eliminate the error entirely ...
Solution 2: Create a performance condition alert on the TempDB Log File. Have SQL Server Agent Service check on the amount of data in the log file. When it reaches say ... 90%, have it run a job to automatically increase the size of the log file by say ... 15%. You should never fill up the log file (unless the hard drive is filled up).
Theres also an old blog entry at http://www.sqlteam.com/item.asp?ItemID=5371 that gives some slightly more labor intensive ideas on avoiding 9002.
July 31 Permissions by User in the DatabaseSo, while reading through questions from students I came across another SQL script request: 'How do I get a list of my users and their permissions in the database?' The anser is a bit more complicated than my last blog entry. For what it's worth ... here it is!
This was some fun to write! Here again, it uses system tables which may change in future versions.
July 30 Listing Logins by DatabaseSo I was going through some past questions asked by students when I came across this one ... "Is there any way to run a report that will give me a lsit of logins and users for my database?" The answer, of course, is yes. And the code to solve the problem follows:
Once again we are writing a view against system tables, which the courseware says you shouldn't do. But sometimes that's the only way to solve a problem.
July 20 Ironic SQL SolutionIt's not a good practice to write a view based on other views. Each "sub" view is optimized independently, just as a subquery would be. It would be better to rewrite your view to directly reference the tables carrying your information. But suppose it's not your database, you've just walked into the job? How do you tell which views are based on other views? The answer is a simple SQL query:
SELECT t.ViewName AS Secondary View', t.TableName AS 'Underlying View' FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE AS t JOIN INFORMATION_SCHEMA.VIEWS AS a ON t.TableName = a.TableName
I've saved this as a view. Ironic isn't it? I'm using a view based on two views to help me eliminate views built on views! Why did I do that? Because there's a more important principle that overrides building a view on a view ... if the underlying table is a system table use an INFORMATION_SCHEMA view instead. This prevents changes in the systems tables between SQL versions from messing up your custom view when the database is upgraded.
July 05 How do I know which Index to use in a 'Hint'?In my last post I showed you how to use an Optimizer 'Hint' to get a view to sort in a different order ... without using TOP n and ORDER BY (usefull if you have to maintain a legacy SQL 7 server). How did I know to use index number 2? Check the sysindexes table in your database. Try this query:
SELECT o.[name] AS 'Table', o.id,
i.indid, i.[name]
FROM sysobjects AS o
JOIN sysindexes AS i
ON o.id = i.id
WHERE o.[name] = 'Employees'
The '1' in the indid (or index id) column tells us that the Employees table is a clustered index. (If there had been a zero there the table would be a 'heap' or unordered mess of records.) Values 2 through 250 in the indid column indicate nonclustered indexes. So in our example the '2' in the indid column next to the value 'LastName' in the last column means there's a sort (or index) on LastName.
*I will be putting my 'margin notes' on all the tips I report here. You can write the tip in the margin of your Microsoft course manual at the cited module and page. Sorted Views without 'Order By'They tell us you can't put an ORDER BY clause in a SELECT statement that defines a view ... unless, of course, you use the TOP n modifier. In fact, in SQL 7 (remember that?) you can't even use TOP n. So how do you get a sort order? Try using an optimizer hint.
In my way of thinking an optimizer hint is misnamed because it's not a hint, it's actually a command to the optimizer. You can force the optimizer to use a particular index when fetching the records to display. For instance, in the Northwind database the query:
SELECT LastName, FirstName, EmployeeID
FROM Employees
will return employee records in EmployeeID order. This is because the employee records are stored as a clustered index using EmployeeID as the index key. If we want to see the employees listed by last name we can either use TOP n and ORDER BY (in SQL 2000 or later) or an optimizer 'hint' (in SQL 7) using the non-clustered index on the LastName column. Here's how we'd write that latter query:
SELECT LastName, FirstName, EmployeeID
FROM Employees WITH (Index(2))
Remember, you are overriding what the optimizer says is the quickest way to retrieve the data, i.e., you will be retrieving the data inefficiently. But if the boss wants the data sorted by last name ... by last name it will be!
*I will be putting my 'margin notes' on all the tips I report here. You can write the tip in the margin of your course manual at the cited module and page. |
|
|
