Thursday, December 27, 2007

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 < 2

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 Rows > x so you can find tables over a specified number of rows. Also, if there are more than one index you may end up yielding several responses as the entries in both the partition table and the sysindexes table count the rows in ... you guessed it the index.

Friday, December 21, 2007

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.

Tuesday, April 24, 2007

Managing Table Fragmentation - Sql Server 2000

Performance.... 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've managed to get my developers to work their ideas on their own TEST servers, well server is a very liberal term, we have Sql Server Dev edition running on a separate desktop system under their desk, and does allow them to perform similar performing queries against similar data, if they require more data, I simply restore a copy of the db to their test environment.

*disclaimer: no actual butts were kicked in the actual story, the emphasis on kicking is written to enhance the fictional portion of the story.



/*
Object: Stored Procedure dbo.sp_dba_DBREINDEX version 1
Script Author: Francisco Tapia,
Purpose: REINDEX all indexes in a given table or database.

Based off the work from Robert Davis http://www.databasejournal.com/img/sp_dba_DefragIndexes.sql


*/
USE MASTER
if exists (select * from sysobjects where id =
object_id('dbo.sp_dba_DBREINDEX') and sysstat & 0xf = 4)
drop procedure dbo.sp_dba_DBREINDEX

GO
CREATE PROCEDURE sp_dba_DBREINDEX
(
@Table sysname = Null, -- Table in which to defrag the indexes
@ShowDetail bit = 0
)


AS
If (Not Exists (Select 1 From sysobjects with(nolock) where id = object_id(@Table) And xtype = 'U') And @Table Is Not Null) Or @Table = '?'
Begin
Print '''' + @Table + ''' is not a valid table object in this database.' + char(10)
Print 'Proper syntax:'
Print 'Exec dbo.sp_dba_DBREINDEX ' + char(10) + space(5) + '@Table = { [table_name] | ''?'' }' + char(10)
Print 'table_name'
Print space(5) + 'Is the table for which to defragment an index. Table names must conform to the rules for identifiers.'
Print space(5) + 'Table_name is optional, procedure will run for all tables in database if parameter not specified.'
Print '''?'''
Print space(5) + 'This help message will be displayed.'
End
Else
Begin


If @ShowDetail = 0
BEGIN
IF @Table IS NULL
BEGIN
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'', '''', 0) WITH NO_INFOMSGS'
END
ELSE
BEGIN
DBCC DBREINDEX (@Table, '', 0) WITH NO_INFOMSGS
END
END
Else
BEGIN
IF @Table IS NULL
BEGIN
PRINT 'Initiating DBReIndex for All Tables'
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'', '''', 0) '
END
ELSE
BEGIN
PRINT 'Initiating DBReIndex for ' + @Table
DBCC DBREINDEX (@Table, '', 0)
END
END

END

Friday, February 23, 2007

How should I store an IP address in SQL Server?

I had the mis-fortune of working with a database where the developers decided to store the IP address as an integer. I believe they did this in order to help improve the performance of their database, or storage, or possibly both. The front end dealt with all the conversion of IP to INT and vice versa. When I needed to make a little bit of enhancements because the front end was just literally timing out at the amount of data that it needed. I decided to change how I retrieved some of my reports, and thus run into the following link, in where the author shares two functions to aid in the conversion of IP to INT and INT to IP. The article is quite good and even has a few numbers on what the performance is on each style of how one might choose to store the IP address information.

How should I store an IP address in SQL Server?


I know my new years resolution was going to be broken the moment I published the first article of the year :D HA!. I do have an article on working with indexes and hopefully it should be quite good. Stay tuned!

Tuesday, January 02, 2007

Calculating Days of the Week and Accounting Months 5-4-4

Ever needed a quick Date formula? In my world I do not need such funky date formulas all the time, only during the occasional Report week of the year. With that said though, I know others run into this need frequently. I found this while perusing one of my favorite sites, enjoy as there is very good detail and sample code on how to achieve just what you need.

(Link to Database Journal)
Calculating Days of the Week and Accounting Months 5-4-4

Happy New Year!

If this is your first time visiting my blog, welcome! For those who are returning, welcome back! SqlThis is aimed at being a quick "Reference" site. In general I do not write a whole lot of fluff about the topics at hand, just the absolute necessary to help understand the script/example and that's it, in some ways SqlThis is a repository of Tsql Links, but often times when I've run into unique solutions or solutions that I want to revisit, I will post them here too, so it's sort of a mesh of links and articles. I also get tips and tricks from fellow DBAs in the community that will provide me w/ Articles or Links. I hope this year to get a new tip/trick at least once a month. I've been posting to this blog for almost 2 years now. Next month will actually be my 2 year anniversary. And while other sites give away Ipods and Laptops (google microsoft laptop giveaway). I will be providing something much more coveted...

... more tsql code :)