Posts

One sandwich short of a picnic

I had an issue at work the other day, something that seems kind of odd to me. You see in Sql Server 2000 you could setup a view with an order by. Of course this required a TOP 100 PERCENT to be specified at the top of the query. You could then take that view and base new views off of it and all results would be ordered by the original view. In Sql Server 2005 (SP1 or even SP2) everything changed, now you could no longer get the correct order by if you coupled it w/ the TOP 100 PERCENT in your view, but then again, if you do not use a the TOP keyword you can't save your view. The solution to how to get around the issue is in the following blog article. http://executioniseverything.blogspot.com/2007/01/order-by-in-views-sql-server-2005.html A proposed solution has been placed on the MSDN site so you can vote on it. It seems the proposal is to do away with the ability to create ordered by views which technically go against the rules of rational theory.... https://connect.microso...

Watch those servers

If you're an active DBA, you'll find that your mornings are spent reviewing failed jobs and checking to see what else needs watching. At my company we were fortunate to purchase Idera's Diagnostic Manager. It is a good tool for reviewing your databases at a quick glance and identify any areas that need attention, ie. Space constratins, failed jobs and other critical processes. Not everyone has the ability to go out and spend thousands of dollars on a monitoring solution. That's why I really liked the following article by David Bird over at Sql Server Central (one of my favorite Sql Sites) titled SQL Overview IV - DBA's Morning Review (http://www.sqlservercentral.com/articles/Administration/63042/). In it he describes a set of reports that you can use to review your servers quickly during the morning and spend more time fixing problems than reviewing things. I Plan to implement many of these solutions in my own environment and see how well it stacks up to our p...

Change your Recovery Model

On occasion you may be tasked with switching your recovery model for a database. This task is very trivial and you may already know how to do this via Sql Server Enterprise Manager or even Management Studio. But you can do this via Tsql as well (as with all things sql server related) The syntax is basically ALTER DATABASE Northwinds SET RECOVERY SIMPLE The recovery Models available are, Simple, BulkLogged and Full. The Simple Recovery model is as you guessed the basic recovery model which pretty much allows you to recover your database from a full backup. The simple recovery model is ideal for test and development databases where data loss is not generally a concern. You may wish never to use this model in a production environment unless the business is willing to loose up all data changes since the last Full or Differential backup. In this model you do not have to maintain the Transaction log as nothing is generally written to it. The BulkLogged Recovery model will log e...

Defrag or don't defrag that is the question!

Back in 2005 I wrote a quick 'n' dirty post (really) about how to maintain indexes. That applied to Sql Server 2000 well enough but did not even touch what was happening with Sql Server 2005. Instead of writing a separate article on 2005, which now come to think about it, I may want to. I instead opted to update my older post, and it now reads more like an article. I've included the link below so that it's quick / easier to find. In this article I also posted up my own defrag monitor script, which should be helpful in locating defraged indexes and that way you can easily defragment only the ones that matter. Good Luck! http://sqlthis.blogspot.com/2005/04/defrag-those-indexes-maintenance.html

How many rows are there?

This question comes up quite a bit when DBAs are faced with space issues... the simple solution most people say is to do a quick SELECT COUNT(*) FROM , but that ends up being such a costly solution and heavy I/O cost if you have a lot of tables or a lot of data. Fortunately you can obtain the number of rows without having to run such a heave I/O intensive operation. In Sql Server 2000 the command looks like this: Select OBJECT_NAME(ID), rows From sysindexes Where id = OBJECT_ID( TableName) AND indid While I found that the number of rows will differ slightly it is close enough to make needed judgment calls based on row count. (I.E. defragging, partitioning, archiving, etc.) In Sql Server 2005 the command looks like this: SELECT OBJECT_NAME(object_id) TableName, rows FROM sys.partitions WHERE OBJECT_NAME(object_id) = TableName note, be sure to replace TableName with your name in order to locate the rowcount for the individual table. You can alternatively modify the Where clause to be by...

We don't care about no stinking buffers

While you work on your newest indexes / table design it is often necessary to clean up the cache on Sql Server to ensure that your changes are actually making a difference. Typically to clear the cache you'll want to run, DBCC DROPCLEANBUFFERS. If you are also executing your test via sprocs you'll want to clear that cache as well to do that just run, DBCC FREEPROCCACHE. This ensures that you are always starting from the same point at each run. If you do not clear the cache between tests you can't be sure that your time results are accurate.

Managing Table Fragmentation - Sql Server 2000

P erformance.... it's an intangible and it seems that no matter how fast and how optimized you set your system up, it seems that there is always new information telling you what you should be doing as well. It seems that there really is no end on to what you can do with software, and when you think you've reached the end of that intangible, then there are all the sorts of things you can do in the hardware world to further push the performance limits. Don't get me wrong, I dig being able to find these tidbits... these jewels of information. It just seems sometimes that you've completed implementing something when now new information, could have helped you out before you started planning. So, What do you do when you continue to manage more and more Sql Servers in your environment? I started out with 8 unhealthy servers. I kept on them and have been able to kick the developers square in the butt!* Part of the butt kicking allowed me to set new rules in place. I...