SQL This!

Lighting Quick Table RowCounts for your Tables

2011-01-17T11:48:00.001-08:00

If you are familiar with TSQL and want to get a row count from your tables you might be tempted to run a simple

Select count(*) from myTable

This however can be a lengthy wait if you have millions and millions of records. Not to mention the potential impact on a production system. Instead consider selecting your rowcounts from your system tables

SELECT OBJECT_NAME(id), ROWS, indid
FROM sysindexes
WHERE indid < 2
AND OBJECT_NAME(id) IN ('myTableName')

Looking forward beyond Sql Server 2005 and beyond the following select should be used and will provide you with the needed information as system tables access may be depracated

SELECT DISTINCT OBJECT_NAME(P.object_id) AS [Name], ROWS FROM sys.indexes I
INNER JOIN sys.partitions P ON P.object_id = I.object_id AND P.index_id = I.index_id
WHERE I.index_id < 2
AND OBJECT_NAME(P.object_id) IN ('myTableName')

So why am I searching for index smaller than 2? In short from the article below you'll find that pure data pages have indid=0, and clustered index pages and the data pages have indid=1, so searching for this information as < 2 covers us. I also took the liberty of adding a distinct. I've not currently read why sometimes there may be duplicate rows, but the objectid of the table may be different and doing a Distinct helps to narrow the results to data you care about.

http://support.microsoft.com/kb/75191

New Look... New Stuff

2010-08-23T10:44:00.000-07:00

Hi everyone... So some new things are going to be happening with my blog... I'm going to pick some helpful hints that help me and my fellow DBA's with what we do on a Daily or weekly basis. These should be helpful for all but you are more than welcomed to share your experiences on what Tips help you the most. One thing that is comming is an article on Wait times... I had the pleasure of trying out Confio's Ignite software. And while it does some really great things, such as pointing out where the bottle necks are for performance on your server. It can be pretty pricey and really does not introduce anything you can't already do.

I'm planning on rolling out a short but continuous list of I/O wait times what they are and how to use them to boost performance on your SQL Server within the coming weeks, I will not concentrate on Sql Server 2005/2008 alone, I know that is the trend in the tech community but there are still many organizations that use Sql Server 2000, I mean... why upgrade Legacy systems and pay more money when those systems are perfectly fine, and fine tunned? Sometimes all that is needed is a little tunning :)

iPad Wifi Networking Issues

2010-04-26T08:32:00.001-07:00

I had recently been reading a lot about the the wifi issues surrounding the iPad, and though this post is not SQL related, it kinda is for me. You see I was using a Remote Desktop app on my iPad when the connection started to drop out on me. Something new that I had started to experience. I determined that it had to do with bluetooth being enabled. I tried disabling it and the iPad returned to it's normal bandwidth loving behavior... the thing was that I was using a bluetooth keyboard with it to type up my sql... so I had to find an alternate solution...

Solution one... you can actually just turn up the brightness on your iPad and it will keep the wifi from going into sleep mode.. wierd right?

or in my case so I didn't need to fumble around I decided to changes some wifi settings of my TrendNet Router, I found the settings at the macrumors forum,

http://forums.macrumors.com/showthread.php?t=889348&page=6

the settings on my wireless that fixed my problem (apparently permanently)

Under the Advanced Wireless Settings...
Change the Beacon Interval to 75
Fragmentation Threshold to 2304
RTS Threshold to 2307

To this I say to the original poster (WesleyB).. great find! Oh sure I could just use the virtual keyboard and not mess with my router, and sure I could just turn up the brightness on my device... but I love just picking up the iPad and quickly logging in to my Sql Server and pick up right where I left off. no fuss, no muss...

New Ranking tutorial

2010-02-17T07:25:00.000-08:00

This is kinda cool. I currently do not have a need for it as my job does not require me to write new tsql code on a daily basis anymore. However, when I used to write ranking code it was always a LONG and TEDIOUS process not really because of the Ranking code, but mostly because of what it takes to get to the ranking code. ARG! My Ranking routine believe it or not consisted of a small segment of code with a CURSOR!

This is an snippet of code from what I used to use and is actually still in production today

declare tstcur CURSOR FOR SELECT id FROM @temp
declare @rank as integer
declare @tID as integer
OPEN tstcur
FETCH NEXT FROM tstcur
INTO @tID
WHILE @@FETCH_STATUS = 0
BEGIN

select @rank = count(*)+1 FROM @temp WHERE final_score > (select final_score from @temp where id = @tID)
insert into @temp (id, Division, responses, max_points, total_points, final_score, rank)
select id, Division, 0, max_points, total_points, final_score, 0 from @temp where id = @tID
update @temp set rank = @rank where id = @tID

FETCH NEXT FROM tstcur INTO @tID
END
CLOSE tstcur
DEALLOCATE tstcur

as you can see it's not really elegant but it DID get the job done. IF you need this type of code you may certainly reproduce it for your own Sql Server 2000 needs.

I do prefer the new method of Ranking using Sql Server 2005 (or 2008). That is the Ranking function built right into the TSQL language so the above code would look like this:

SELECT RANK() OVER (
ORDER BY final_score) AS [Rank],
id, Division, responses, max_points, total_points, final_score
FROM @temp

I quite like the non-cursor solution, and if you've read other articles by me you'll know that I'm not at all a fan of cursors (they're just plain evil!) :)

For a full tutorial on how to use the new Ranking feature... check out
Ranking Functions in Sql Server 2005 and 2008

Rename a Sql Server Database

2009-12-02T07:22:00.000-08:00

Often when a database needs to be renamed a common tool that I've used in the past was always to use the sp_renamedb procedure. And that's one of the reasons I love the internet, there is always a better way to do something. Take the tips on how to rename your database without the use of this procedure. What is neat about the following article is that it also changes the logical and physical names of the database.
see more at this link...
http://www.mssqltips.com/tip.asp?tip=1891

Boosting Performance

2009-08-11T22:13:00.000-07:00

Fixing smallish databases which are less than 1-2 gb may be just annoying when you are dealing with multiple indexes, but try managing some of those larger ERP databases with literally thousands of tables! Talk about a database from hell, having to sift through 10 of thousands of indexes can be a real chore if you're searching for performance bottle necks. There are some great solutions out there that all cost money per instance or per site license and can get quite pricey. But just about all of those products are charging you for something you can do on your own.

The article below is an extension of my previous blog on maintaining those indexes It's the script that has evolved from some very basic loops and DMV ( dm_db_index_physical_stats ). In my script (follow the link below) You'll find that I chose to stick to a SAMPLED stats, which essentially looks at the number of compressed pages. If you're talking millions of row of data and a very short maintenance window, you're going to want to stick to SAMPLED, otherwise, you'll opt for the full DETAILED search if

A) your maintenance window is greater than the amount of time it takes for a DETAIL report to be returned
B) your database is small enough

One of the problems that I've ran into with my production databases that use a FULL recovery model is the maintaining the size of the transaction logs. The problem is that I COULD switch them over to be SIMPLE recovery models, and then the transaction log would not grow out of control during the rebuild / reorganization (Defrag) of the indexes, but I've found this not to be an ideal situation, since in my organization even though I have a maintenance window on the weekends, the business comes first, so if they require access to their data, I must make it available. Having good backups that allow the company to return up to the minute before failure using the FULL recovery model is important to us. So in the script (see link below)

Keep Your SQL Server Indexes Fragmentation Free

My DevX article was all about Sql Server 2005 and Sql Server 2008, but what about Sql Server 2000? surely you don't want to be stuck just running a performance monitor on your all your 2000 servers right? Here is the updated 2000 script to find all fragmented tables in 2000 along with the DBCC DBREINDEX wich will rebuild them for you.

Catch-22: if the table has less than 1000 rows, a rebuild will not change anything and you may be left with having to drop and recreate the index manually anyways.

The Code:
...I created a temp table manually to capture the results of SHOWCONTIG which I pipe to a grid result using the WITH TABLERESULTS hint, next this is joined against the information schema tables table. In the Where clause I specified that we are interested in 'Base Table's only so that we don't get a lot of system table specific stuff. I also added the cirteria that we want to see only fragmented tables of 5% and that have over 1000 rows. In my case my legacy databases are all well under 50gb and thus this query makes sense. This example use a cursor, as it's an old script, but I'll post up a cursor-less version as I prefer cursor-less solutions.

As always watch those log files when you are defragging indexes!

USE YourDatabaseName--Enter the name of the database you want to reindex

CREATE TABLE #db_defrag
( ObjectName VARCHAR(255),
ObjectID INT,
IndexName VARCHAR(255),
IndexID INT,
[LEVEL] INT,
Pages INT,
[ROWS] BIGINT,
MinRecordSize INT,
MaximumRecordSize INT,
AverageRecordSize INT,
ForwardedRecords INT,
Extents INT,
ExtentSwitches INT,
AverageFreeBytes DECIMAL(20,2),
AveragePageDensity DECIMAL(20,2),
ScanDensity DECIMAL(20,2),
BestCount INT,
ActualCount INT,
LogicalFragmentation DECIMAL(20,2),
ExtentFragmentation DECIMAL(20,2))

DECLARE @sql AS VARCHAR(100)

SET @sql ='DBCC SHOWCONTIG WITH TABLERESULTS'

INSERT INTO #db_defrag
EXEC (@sql)

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables tbl
INNER JOIN #db_defrag dfrg ON tbl.table_name = dfrg.ObjectName
WHERE table_type = 'base table' AND dfrg.LogicalFragmentation >= 5 AND [ROWS] > 1000
ORDER BY LogicalFragmentation DESC

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DBCC DBREINDEX(@TableName,' ',90)
SET @sql = 'UPDATE STATISTICS ' + @TableName
EXEC (@Sql)

FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

DROP TABLE #db_defrag

UTO! Unidentified Table Object

2009-07-21T09:47:00.000-07:00

It's been a while since I've updated the blog, but I Did want to mention that I am working on a neat article, which focuses on my passion for performance. Stay tuned for the latest details...

in the mean time, have you ever been stuck with someone else's database? Or how'bout a vendor database where someone needs you to extend a task. Well finding the stored procedures is relatively simple. Remember just go through profiler, run the process and you can monitor which stored procedures are called sometimes this also provides you some feedback on which tables are being accessed. Other times you may need to report on some of this information, so you may need to search the database on where they decided to store such information. I extended my own version of Narayana's searchalltables procedure, in this new version you'll notice that you get to also search text (and ntext) fields along with only a single while loop.

Check out the latest script and article here:
Searching Unidentified Tables and Columns for Specific Content

Mail Call!

2009-06-23T11:10:00.000-07:00

Email, You use it, your colleges use it, even your systems use it. It's a part of everyday business. If you are a Sql Developer you have probably figured out how to implement email already, Often times I've seen many DBA's and Developers implement it from outside of SQL Server in rather ingenious ways...

This article outlines how to setup Sql Server Mail in Sql Server 2000 and 2005 (2008 is the same as Sql Server 2005). By bringing mail inside of your server you can now send reports, alerts and other needed information based on the triggers and alerts that matter to you most. Making execution calls to xp_cmdshell to an opensource program (Blat) and sp_send_dbmail calls for Sql Server 2005 help leverage reporting from Sql Server.

Sending E-mail from SQL Server 200X

Limit your responses , please.

2009-05-21T21:23:00.000-07:00

When you are faced with request from users who will ask things like... i want to know the top 2 machines of every model type that have active leads, you may find yourself baffled and stunned to find that the Select TOP n does very little to help you out. The following article address the issue completely whether you're a sql developer or NOT. For myself it was a new look at existing solutions that we had employed all which were cumbersome and tedious to maintain, the solutions in the article describe the best approach which is easily extensible and flexible.
Limit Groups by Number Using Transact-SQL or MS Access

MSDE enable TCP/IP or Named Pipes

2009-04-09T11:37:00.000-07:00

When you inherit a new server sometimes you find that you can't connect to the server, to fix that you may need to simply enable the protocol via Sql Server Network Utility (svrnetcn) that is listening For MSDE.

In Windows, click Start and Run.
Enter svrnetcn and click OK.
Under the General tab, verify that the correct instance for the server is displayed in the Instance(s) on this server box.
Highlight your desired protocol and click Enable (double clicking the name also moves the protocol to the enabled protocols box).
Click OK.
Restart the Sql Server Instance
In Windows, click Start and Run.
enter services.msc
Locate the MSSQLSERVER instance you modified in the Sql Server Network Utility and Restart the service.

You may wish to ensure that your users are not logged on or at least notified of this change as it will kick them out of the application

SSIS SOS!

2009-03-17T22:04:00.000-07:00

If you have been regretting making the move from Sql Server 2000 to Sql Server 2005, you will be in complete shock and awe when you find that you no longer have DTS to move your data around. The good news is that in Sql Server 2005 you get Sql Server Integration Services (SSIS) a full feature ETL product for Sql Server 2005. If you want to move things moving quickly take a look at the Sql Server 2005 Import / Export Wizard short article which outlines just how easy shuffling your data around can really be.

Comma delimited Lists

2009-03-17T11:56:00.000-07:00

Take the post about finding columns and incorporate it with this post from a college of mine.
http://www.artfulsoftware.com/infotree/tip.php?id=753

Now you're off and running turning lists to comma lists, in corporate that with the list table function I posted sometime back, and you can go backwards and forwards!

Find me a column...

2009-03-17T11:43:00.000-07:00

Forgive the fact that the following SQL is not formatted, but this topic came up today on a list that I frequent, which was how to get all the fieldnames of a view (or table etc). this dandy little select statement will gather just that and all you need to really parse it for is your table name or column name... maybe you want to know how many times you named something "EntryTime" this is an easy way to find all that information at a glance.

This is a Sql Server 2005 version (If you need it I can post a Sql Server 2000 version as well.)

SELECT t.name AS TableName,
sc.name AS SchemaName,
c.name AS ColumnName,
c.column_id AS ColumnID,
c.precision AS [Precision],
types.name AS TypeName,
basetypes.name AS BaseTypeName,
st.name AS TypeSchemaName,
CASE WHEN c.max_length>=0 AND basetypes.name IN (N'nchar', N'nvarchar') THEN c.max_length/2 ELSE c.max_length END AS Length,
c.scale AS Scale,
CONVERT(bit, c.is_identity) AS [Identity],
CONVERT(bit, c.is_computed) AS [IsComputed],
CONVERT(bit, ISNULL(ic.is_not_for_replication,0)) AS NotForReplication,
CAST(ISNULL(ic.seed_value,0) AS DECIMAL(38)) AS IdentitySeed,
CAST(ISNULL(ic.last_value,0) AS DECIMAL(38)) AS IdentityCurrent,
ISNULL(ic.increment_value,0) AS IdentityIncrement,
CONVERT(bit, c.is_nullable) AS AllowNulls,
c.default_object_id AS DefaultTextID,
ds.name AS DefaultName,
c.default_object_id AS defaultid,
dsc.name AS DefaultSchemaName,
CONVERT(bit, CASE ds.parent_object_id WHEN 0 THEN 1 ELSE 0 END) AS IsBoundDefault,
rs.name AS RuleName,
rsc.name AS RuleSchemaName,
CONVERT(bit, CASE WHEN fi.language_id IS NULL THEN 0 ELSE 1 END) AS FullTextIndexed,
cc.definition AS ComputedText,
c.is_rowguidcol AS IsRowGuidCol,
c.collation_name AS Collation,
fi.language_id AS FullTextLanguage,
fisc.name AS FullTextTypeColumn,
c.is_xml_document AS XMLDocument,
xscs.name AS XMLCollection,
xsc.name AS XMLCollectionSchemaName,
t.type AS type,
CONVERT(bit, ISNULL(cc.is_persisted, 0)) AS [IsPersisted]
FROM sys.all_columns c INNER JOIN sys.all_objects t WITH (NOLOCK) ON c.object_id=t.object_id
LEFT JOIN sys.schemas sc WITH (NOLOCK) ON t.schema_id=sc.schema_id
LEFT JOIN sys.types basetypes WITH (NOLOCK) ON c.system_type_id=basetypes.system_type_id AND basetypes.system_type_id=basetypes.user_type_id
LEFT JOIN sys.types types WITH (NOLOCK) ON c.user_type_id=types.user_type_id
LEFT JOIN sys.schemas st WITH (NOLOCK) ON st.schema_id=types.schema_id
LEFT JOIN sys.identity_columns ic WITH (NOLOCK) ON ic.object_id=c.object_id AND ic.column_id=c.column_id
LEFT JOIN sys.all_objects ds WITH (NOLOCK) ON ds.object_id=c.default_object_id
LEFT JOIN sys.all_objects rs WITH (NOLOCK) ON rs.object_id=c.rule_object_id
LEFT JOIN sys.fulltext_index_columns fi WITH (NOLOCK) ON fi.object_id=c.object_id AND fi.column_id=c.column_id
LEFT JOIN sys.all_columns fisc WITH (NOLOCK) ON fi.type_column_id = fisc.column_id AND fisc.object_id=c.object_id
LEFT JOIN sys.computed_columns cc WITH (NOLOCK) ON cc.object_id=c.object_id AND cc.column_id=c.column_id
LEFT JOIN sys.schemas dsc WITH (NOLOCK) ON dsc.schema_id=ds.schema_id
LEFT JOIN sys.schemas rsc WITH (NOLOCK) ON rsc.schema_id=rs.schema_id
LEFT JOIN sys.xml_schema_collections xscs WITH (NOLOCK) ON xscs.xml_collection_id=c.xml_collection_id
LEFT JOIN sys.schemas xsc WITH (NOLOCK) ON xsc.schema_id=xscs.schema_id
WHERE t.type IN ('U', 'FT', 'TF', 'IF', 'S', 'TT', 'V')
ORDER BY c.object_id, c.column_id

Turbo Charge Your SSMS

2009-02-13T08:17:00.001-08:00

The other day a co-worker mentioned one of his biggest pet peeves about Sql's new IDE. Of course he likes the old IDE, but what he detested the most was the slow bloat time it took to get his SSMS loaded on the screen. When we timed the boot time for his Enterprise Manager to load it took nearly 7 seconds. Not bad, but then proceeded to tell me how much longer SSMS (Sql Server Management Studio) took to load. I guessed that it could possibly be about 10 seconds (only 3 more seconds) but I was shocked to see it load in nearly 25 seconds. it was nearly 3x less productive just loading the thing before you could connect to do anything. I couldn't believe that it was nearly that slow on his computer, I had never timed it on my pc. So when I returned I had to try it. Again the load time was nearly 20 seconds on my machine (about 18 seconds to be exact). I was not entirely pleased with my results but thankfully Google provided the answer, one quick search for "Turbo charge my SSMS" yielded the following result which brings that load time to that of Enterprise Manager about 6-7 when you are prompted with the server you want to connect to.

Speed up your Sql Server Management studio

The solution is rather simple. First get rid of the splash screen (unless you really like it.) by adding the /nosplash parameter to your shortcut.

next the real jewel, Go into Internet Explorer's Tools>Options>Advance Tab and look for the Security section, uncheck the following two settings:

o Check for publisher's certificate revocation
o Check for server certificate revocation*

once you have completed these tasks you will notice what appears to be a TurboCharged SSMS!

One sandwich short of a picnic

2008-12-03T14:05:00.000-08:00

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.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249248

Watch those servers

2008-06-06T10:55:00.000-07:00

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 paid solution. I will report back and let you all know!

Change your Recovery Model

2008-06-06T10:34:00.000-07:00

On occassion 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 every event except those that are Bulk transactons. This has almost the same recoverability as a Full Recovery model. With this recovery model you can do full, differential and log backups, you are also capable of restoring up to the minute so long as your last change was not a bulk logged operation, in which case those changes will remain lost. You will need to schedule a regular backup for your transaction log for those operations that are not Bulk transactions.

The Full Recovery model offers the highest amount of protection against data loss. This model allows you to recover any piece of data up to the minute (prividing you have the last backup). You can do full, differential and log backups. You can restore any of the backups for returning to your point in time. Such as a FULL backup followed by a Differential and finally your set of Log backups until the point in time when the failure occurred. In this recovery model you will want to schedule a regular log backup for your transaction, otherwise your database log files will grow and grow.

Defrag or don't defrag that is the question!

2008-03-07T13:08:00.000-08:00

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?

2007-12-27T10:17:00.000-08:00

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.

We don't care about no stinking buffers

2007-12-21T12:09:00.000-08:00

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

2007-04-24T09:13:00.000-07:00

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

How should I store an IP address in SQL Server?

2007-02-23T08:49:00.000-08:00

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!

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

2007-01-02T15:14:00.001-08:00

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!

2007-01-02T14:28:00.000-08:00

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 :)

10 mistakes to avoid

2006-11-16T13:43:00.000-08:00

A very good read for even the most veteran of developers, the following top 10 mistakes list is not necessarily the top 10 mistakes of all time, but it is a good guideline...
SQL Server query design: 10 mistakes to avoid

Vadivel's blog: Avoid using sp_rename ...

2006-11-15T20:27:00.000-08:00

Vadivel's blog: Avoid using sp_rename ...

Finding your group(file)

2006-11-13T16:29:00.000-08:00

Managing tables into filegroups can be a very daunting task when you start to look at huge database systems. The following script always helps me to locate what table is in which filegroup.

It helps to keep things neat and tidy.

SELECT o.name, s.groupname, f.name
FROM dbo.sysfilegroups s
INNER JOIN dbo.sysindexes i
ON i.groupid = s.groupid
INNER JOIN dbo.sysfiles f
ON f.groupid = s.groupid
INNER JOIN dbo.sysobjects o
ON i.id = object_id(o.name)
AND i.indid in (0, 1)

How to spot a big table

2006-10-27T17:12:00.000-07:00

There are plenty of tools out in the wonderful world of the web to provide you with this very detail of information. Do you need it? Well Gui's can be fun and down right useful when in the right environment. In my world I use software to help keep track of my LIVE/Production systems but, what about those other systems that are not really live, but are development and test. Sometimes you start to run out of room, and you wonder why... sometimes you want a quick count of the tables that either consume the most space in terms of disk, or rows... How can you do it quickly?

The following snippet is not groundbreaking, but it does provide a valuable service. More importantly it answer the questions of... which table is bigger... which tables are unused. etc, etc. You can even take this to the next level and keep track of which tables grow the most by surveying all your databases. And if you really like GUIs you can graph a trend on which tables grow the most.. stuff that is already available by vendors for thousands a month.. not groundbreaking like I said earlier but certainly useful and a good exercise to practice. Once you have it you can save a ton of Money on the GUIs out there...

Below I took advantage of using the undocumented stored procedure sp_msforeachtable, and the global variable '?'. Essentially for each table in the database run this sproc (stored procedure).

--By: Francisco
--Date: 10/27/2006
--Purpose: Find Out wich Tables Yield the most space.
IF EXISTS (select * from TEMPDB.dbo.sysobjects WITH(NOLOCK) where id = object_id(N'TEMPDB.dbo.#SpaceUsed')) BEGIN
DROP TABLE #SpaceUsed
END
CREATE TABLE #SpaceUsed (TableName VARCHAR(255),Rows INT, Reserved Varchar(255), Data VarChar(255), Index_Size VarChar(255), Unused VarChar(255))
INSERT INTO #SpaceUsed (TableName, Rows, Reserved, Data, Index_Size, UnUsed)
EXEC sp_msforeachtable 'EXEC sp_spaceused ''?'''

SELECT TableName, Rows, Reserved, Data, Index_Size, Unused FROM #SpaceUsed
ORDER BY CAST(REPLACE (DATA, 'KB', '') AS INT) DESC

How Safe are your passwords?

2006-08-30T13:54:00.000-07:00

Let's Face it, when working with Sql Server it does pose many security threats when unsecured, even while secure you have an ultimate login to try and crack. That of course is the "SA". Unless you set up your Sql Server to NT Authentication only, but sometimes you don't have much of a choice. The following article has some interesting methods to try and upgrade your passwords to be MORE secure.

Find SA Password (Brute Force) Find Passwords (Dictionary) Analysis of Password Security Password Generator ASCII Table

Going against the Trend

2006-08-29T13:51:00.000-07:00

Sql Server 2000 Reporting Services is an awesome reporting tool, but what they forgot to pack in is something simple like a 2nd Y axis (wich I still haven't figured out how to do), and a stinking Trendline. I mean, c'mon, even EXCEL does trendlines! So my quickie solution is the following script, All you have to do is populate the @TrendTable and you're on your way!!!

DECLARE @TrendTable TABLE (PID INT IDENTITY (1,1),
XColumn smalldatetime,
YColumn1 REAL,
YColumn2 INT,
Trend REAL)

---------
/*
At this point populate the @TrendTable with your data, the Xcolumn is of course plotted data on the x axis, On this example I've put in a Y column and a 2nd Y column for your charting needs, YColumn1 is the column that will be trended.
*/
--------

DECLARE @n REAL
DECLARE @sumX REAL
DECLARE @sumY REAL
DECLARE @sumXY REAL
DECLARE @sumX2 REAL
DECLARE @sumY2 REAL
DECLARE @sumX_2 REAL
DECLARE @sumY_2 REAL

DECLARE @m REAL
DECLARE @b REAL
DECLARE @r REAL

SELECT @n = COUNT(*), @sumX=SUM(PID),
@sumY=SUM(YColumn1),
@sumXY=SUM(PID*YColumn1),
@sumX2=SUM(POWER(PID,2)),
@sumY2=SUM(POWER(YColumn1,2))
FROM @TrendTable

SELECT @sumX_2 = POWER(@sumX,2), @sumY_2 = POWER(@sumY,2)

--slope
SELECT @m = (@n*@sumXY-@sumX*@sumY)/(@n*@sumX2-@sumX_2)
--y intercept
SELECT @b = (@sumY-@m*@sumX)/@n
--r
SELECT @r = (@n*@sumXY-@sumX*@sumY)/SQRT((@n*@sumX2-@sumX_2)*(@n*@sumY2-@sumY_2))

UPDATE @TrendTable
SET Trend = @m * PID + @b

SELECT * FROM @TrendTable

The myths and legends of the holy land of the database | The Register

2006-06-02T09:08:00.000-07:00

Don't end up in urban legend hell!, The following articles really do bring to light the urban legends of database engines. Read along and get de-mystified. These all List to the Register's site.

The myths and legends of the holy land of the database

The parable of the beer and diapers

Tackling Apache zombies

Microsoft was caught stealing secrets from Borland

Intellisense for SQL Server

2006-05-24T14:51:00.000-07:00

I dunno if you've heard of Red-Gate. but so far they've created some very top notch software to help out with both webservices and Sql Server. I've personally tried both their Sql Data Compare and Sql Compare wich helps match both schema and data accross diffrent databases and servers. This has many benefits as you can easily work in a test environment and have the ability to work with some close to live data. and after you've completed your work you can propagate the changes to your test environment with out a hassle not to mention you can minimize the time it takes to roll changes to your live environment.

But really what I wanted to talk about today was Sql Prompt. It's Intellisense for Sql Server. That's it. Well that's not just it. It loads a systray app in your system and hooks directly to your Query Analyzer. Off the cuff I found it to be extreamly effective. I started to write my Select statement and it was able to appropriately give me back names of tables to complete and even name of stored procedures that were available to me. One of the options that I liked the best, was the join feature, in which it helps you complete a join statement super quick by giving you a small list of all available joinable fields. you can type at your desire or pick from the list... ultra cool.
Well now for the hook and sinker... It's Free :). Unlike the other products that help make them money. Probably the most useful app they have on their site is completely free. Of course after using this little proggy you're gonna probably want to return and see what other cool apps they have. To get your copy click on the following link:
SQL Intellisense, Intellisense for SQL Server - SQL Prompt

After using it for a few minutes I was able to get the hang of it. Extra niceties are things such as auto-completion for your parameter names for a sproc (stored procedure). nice touch guys!

btw, nope I do not work for Red-Gate, I just happend to have really liked a few of their Software Titles. :)

---*update*---
SQLprompt will only be free until Sept 1st.

Find and Replace

2006-05-01T11:53:00.000-07:00

I cruised the net last week looking for a simle "Find and Replace" script. sorta like a split for VB only that I need the replace to occur at every nth character. Not finding what I wanted I ended up writing my own, the idea was that I'd start at the lenght that my string needs to be then crawl backwards until I find a space (or breakpoint) then replace it with my Replace string. There may be a better way.. if you have one feel free to comment.

CREATE FUNCTION dbo.SplitAndReplace
(@String AS VARCHAR(8000), @SplitString CHAR(1), @ReplaceString VARCHAR(255), @BreakInterval AS INT
)
RETURNS VARCHAR(8000)

AS

BEGIN
/*
SET @String = 'This is as very simple text that needs to be broken down for processing in the external system, the process should auto insert an exclamation-pound sign in lew of a space that is at 60 characters'
SET @BreakInterval = 60
SET @ReplaceString = '!#'
SET @SplitString = ' '
*/
DECLARE @X AS INT
DECLARE @Y AS INT
SELECT @X = 0, @Y = 0

WHILE (LEN(RTRIM(LTRIM(@String))) - @Y) / @BreakInterval > 0
BEGIN

WHILE @SplitString <> SUBSTRING(@String, @Y + (@BreakInterval - @X), 1)
BEGIN
SET @X = @X + 1
END

SET @String = STUFF(@String, @Y + (@BreakInterval - (@X )) , 1, @ReplaceString)
SET @Y = @Y + (@BreakInterval - @X)
END
RETURN @String
END

How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field

2006-01-19T16:24:00.000-08:00

Sometimes you just have to know How to Set Up a SQL Server Stress Test. SQLIOstress is a bit diffrent than SQLIO wich actually gives you back only IO resutls, whereas SQLIOstress will yeild results in the manner that SQL maintains files.

Migrating Logins from One SQL Server to Another

2006-01-18T09:08:00.000-08:00

UPDATED! 11/11/11
Every DBA runs into the following topic from time to time in their career, How Do you go about Migrating Logins from One SQL Server to Another? From time to time you'll want to move actual sql login users to another server be it an upgrade, or if you have a good development - test - production cycle, you might be more used to this. Or maybe just maybe you're starting this cycle out, initially just run the Sproc provided by Microsoft called sp_help_revlogin There are two methods posted on the Microsoft site:

Method 1 is for Sql Server 2000 to 2000 KB 246133

Method 2 is for Sql Server 2000 to 2005 KB 246133

Method 3 is for Sql Server 2005/2008 KB 918992

Copy the results and paste them to the new query window

Go to the source server and open up a new query window and be sure you are pointing to your source database
check that your Management studio is set to return your results as text
you can do this by right clicking within the query window then choosing results to text
Paste your script into the query window and execute the script by clicking the run button or F5.
copy the results and open up a new Management Studio Query window that is connected to your Destination Server and be sure you've selected the destination database.
Paste the result text, and edit out any users you don't want to re-create

Now that you've migrated the users you can simply backup your database as you normally do and restore it to the new environment.

These steps are only necessary if you need to migrate users from one physical server to another. You won't need to run these steps if you are on the same server and you've simply created your test database in the same environment, the users already exist in the database from your backup and restore, and they already exist in the server system database as well. This is only necessary when you have two completely separate servers/instances.

Good luck and happy TSQLing

Access/SQL:How to satisfy the underlying record

2005-09-02T08:18:00.000-07:00

Even tho this is a Sql Blog, the following actually is related...

Many developers will often use Microsoft Access as a viable Front End to Sql Server instead of using VB or even VB dotNet. This is often a quick way to leverage rapid development. However if you've ever used Access you'll quickly find that it's not the friendly little IDE that it claims to be. When you attempt to leverage it's power as bound data application, you'll often find yourself wrestling within the clutches of it's Before Update, Current and After Update Events. I can say you haven't known how to really hate, until you've battled it out with these events. In a simple application it all works fine, in a typical Access MDB format, binding data to forms is common and relatively easy and painless. Try it with an ADP (Access Data Project).

The Before Update event can easily be avoided and replaced with a database trigger. Trigger's to Sql Server are what Before Update events are to Access. If your changes can be made server side, it's often the best plan of attack. But what if in your trigger you update other tables? if you do you'll most certainly be faced with the dreaded "The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source." followed by quickly seeing the data you just keyed into your Access Form disappear. Seems like it'd be easier to wrestle w/ the Before Update and Current Events. The practicallity of doing a trigger is that you don't need to make another round trip to the database. For Access the above error occurs because Access uses Sql's @@Identity to synchronize with the Front end. It's best outlined in KB article:KB275090.

My workaround? I created a temp table and filled it with the target table's PKID

CREATE TABLE #reIdentify (JUNKID int IDENTITY (1, 1) NOT NULL)
SET IDENTITY_INSERT #reIdentify ON
INSERT INTO #reIdentify(JunkID)
SELECT MyTablePKID FROM INSERTED
SET IDENTITY_INSERT #reIdentify OFF

Now I can have my Before_Update event occuring at the server level saving me client processing and battling yet another Access Bound Event.

How to setup xp_cmdshell

2005-08-02T23:14:00.000-07:00

Setting up xp_cmdshell securely can be a challenge. Generally only sysadmins can execute the xp_cmdshell. It is actually a very huge security hole if you choose to program your website w/ the sa account as you lay your database in the path for SQL injections. This can be typical if the originating server or application is one that you are taking over.

However, there will be instances where you will need to grant mere mortal users access to the xp_cmdshell, this can be done via the sql agent proxy, this gives you the ability to assing a generic nt user the ability to execute a limited range of file object manipulation. The following Microsoft article goes into the details for setting up your SQL Server agent proxy.

How to configure a SQL Server Agent proxy account to enable non-system administrators to execute the xp_cmdshell extended stored procedure in SQL Server 2000

Table Compare

2005-07-29T15:41:00.000-07:00

When you need to quickly find out diffrences of data/fields between two tables, there is nothing easier than this script:
Table Compare

may the CODE be with you....

2005-07-20T10:17:00.000-07:00

Here is an excellent collection of code snippets and useful libraries for just about any task you're likely to encounter. I've included the link below:

My code library (SQL Server T-SQL code samples, snippets, examples, tips, tricks, stored procedures, user defined functions, VB programs): Narayana Vyas Kondreddi's home page

What's up doc?

2005-06-03T16:06:00.000-07:00

The following SELECT statement displays how long a sql server instance was
up for; SQL Server refreshes the tempdb every time it restarts. To
determine how long a SQL Server instance has been running, we use the
created date of the tempdb.

SELECT
CAST(DATEDIFF(ss,crdate,GETDATE())/60/60/24 AS VARCHAR(4)) + 'd '
+ CAST(DATEDIFF(ss,crdate,GETDATE())/60/60 % 24 AS VARCHAR(4)) + 'hr '
+ CAST(DATEDIFF(ss,crdate,GETDATE())/60 % 60 AS VARCHAR(4)) + 'min '
+ CAST(DATEDIFF(ss,crdate,GETDATE()) % 60 AS VARCHAR(4)) + 'sec' AS
SQL_Server_HAS_BEEN_UP_FOR
FROM MASTER.dbo.SYSDATABASES
WHERE name = 'TempDB';

-By Billy Pang

Searching Stored Procedures Source Code

2005-05-31T16:05:00.000-07:00

I found this article while searching for some "text" in my own stored procedures, (the article link is available at the bottom.)
---------------
October 6, 2000
Searching Stored Procedures Source Code
By Alan Enderby

Want to find that stored procedure that deletes those stock items.

This simple (& crude) script will scan syscomments for a given string
and display the stored procedure and the section of code.

if exists (select * from sysobjects where id =
object_id('dbo.sp_findsp') and sysstat & 0xf = 4)
drop procedure dbo.sp_findsp
GO

create proc sp_findsp @s varchar(255) as
DECLARE @msg varchar(255) ,@ul varchar(255)
select @s='%' + @s + '%'
select 'SP Name'=upper(o.name), Seq=colid ,'SP
Line'=substring(text,patindex(@s,text)-5, 30)
from syscomments c , sysobjects o
where o.id=c.id
and patindex(@s,text) > 0
order by name
SELECT @msg='* Stored procedures containing string "' + @s + '=' +
convert(varchar(8),@@rowcount) + ' *'
SELECT @ul=replicate('*',datalength(@msg))
Print ' '
PRINT @ul
PRINT @msg
Print @ul

GO
-----------
Searching Stored Procedures Source Code

the Doppelganger

2005-05-12T08:54:00.000-07:00

Data scrubbing is a fact of database life. Whenever you import and normalize data you do run the risk of duplicate data imports as well. This can be especially true if your source database had few restrictions on preventing duplicate data entry. In one particular table I had many rows with duplicate data down to the entrytime field; everything was identical except for the PKID. I tried the typical Cursor script found in various websites, but that would Yield an estimate of 14hrs while the test server processed all 16k rows! I could not very well do that to the production server, on my quest I tried several while loops and eventually came up with this select statement which for my situation (2 duplicate rows for 1 real row of data) First thing I did was dump out all the duplicate rows PK's and FK's into a temp table called #Dup, I hit upon this select statment and I sucessfully narrowed down 16k rows down to 8k of unique data, and was able to delete the duplicate FK's based on the PKid.

SELECT A.PKID
FROM #Dup AS A
INNER JOIN (SELECT FKID FROM #Dup GROUP BY FKID) B
ON A.FKID = B.FKID
AND A.PKID IN (SELECT TOP 1 PKID FROM #DUP C WHERE A.FKID = C.FKID)

The Select Statement finds one of the Duplicate tables, you can use this statement to delete only these rows leaving a unique record behind.

Month Variable Table

2005-04-18T14:38:00.000-07:00

Every once in a while you need a quick Table w/ a range of dates, that's what this is for...

Declare @iLoop AS INT
SET @iLoop = 0
DECLARE @rptMonths TABLE(rptMonth DateTime)
WHILE @iLoop <= DateDiff(mm, @StartDate, @EndDate)
BEGIN
INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, @iLoop, @StartDate))
SET @iLoop = @iLoop + 1
END

Get Workday count with out weekends

2005-04-13T14:37:00.000-07:00

This has come up before in my line of work, to get a total workday count but with out the weekends.

(DATEDIFF(dd, StartDate, EndDate) + 1)
-(DATEDIFF(wk, StartDate, EndDate) * 2)
-(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS TotalWorkDays

Defrag Those Indexes - Maintenance

2005-04-07T15:27:00.000-07:00

This article was written back before I was looking into Sql Server 2005. The underlying idea is the same, in order to keep your database running healthy you will need to maintain and administer the underlying architecture. In this short but subtle refresh I've separated the 2000 concepts and implementations from those used today in Sql Server 2005. If you have comments or corrections do feel free to email me or leave a comment.

SQL Server 2000 =====================================================
It is imperative you maintenance your Database. One way to check up on the indexes per table is to run the DBCC SHOWCONTIG command as DBCC SHOWCONTIG ('tbl_YourTableName') with fast,ALL_INDEXES

You'll end up with a very similar display like the following...

DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 1680
- Extent Switches..............................: 217
- Scan Density [Best Count:Actual Count].......: 96.33% [210:218]
- Logical Scan Fragmentation ..................: 0.18%
DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 480
- Extent Switches..............................: 64
- Scan Density [Best Count:Actual Count].......: 92.31% [60:65]
- Logical Scan Fragmentation ..................: 0.83%
DBCC SHOWCONTIG scanning 'tbl_YourTableName' table...
Table: 'tbl_YourTableName' (1113627606); index ID: 5, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 696
- Extent Switches..............................: 95
- Scan Density [Best Count:Actual Count].......: 90.63% [87:96]
- Logical Scan Fragmentation ..................: 0.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What you're really looking for is the Scan fragmentation to be as low as possible. I've also read on other sites that you want the Scan Desity to be as close to each other as possible like for example 87:96 is fairly close and gives you a density over 90%.

From that you can easily run a defrag on each index as follows

DBCC INDEXDEFRAG (8, 1113627606, 5)
DBCC INDEXDEFRAG (8, 1113627606, 2)
DBCC INDEXDEFRAG (8, 1113627606, 1)

More Information can be obtained via: Sql Server Performance\dbcc commands
And sql-server-performance.com/rebuilding_indexes
and here, SQL Server Index Fragmentation and Its Resolution

If you just wish to defrangment the entire database this little script will put you on your way: (from the link above sql server performance site)
SET DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

------END SCRIPT
SQL Server 2005 ===================================================
Fastforward to today,
IndexDefrag still works in 2005, but if you read around the 'net you'll find many more references to choose to rebuild your indexes via the ALTER INDEX command, which is new for SQL Server 2005.

Before you go out and begin de-fragmenting tables like crazy, it's best to have a plan, finding out how much fragmentation is in your table in 2005 is easier too with the newer function that provides this information. below I've expanded on what you can find around the internet, the below script will allow you to identify rows over 100k and with more than 10% fragmentation, I also handled the commonly seen error for databases that are of the standard dictionary order (80), and the common "Error near '(' ".

/script/
SET NOCOUNT ON
DECLARE @db_id SMALLINT
SET @db_id = DB_ID()

SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS TableIndexName,
phystat.avg_fragmentation_in_percent, rows
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') phystat
inner JOIN sys.indexes i WITH(NOLOCK)
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id
INNER JOIN sys.partitions p WITH(NOLOCK)
ON p.OBJECT_ID = i.object_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND ROWS > 100000
/end script/

More information on this system function is availble here:
http://technet.microsoft.com/en-us/library/ms188917.aspx
This link will even give you a cool little script you can use to find out what tables in your database are fragmented over 40%, which is very useful if you are planning a weekend of maintenance.
http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx

Now that you have the needed information on how fragmented the indexes are you can begin defragmenting using the Alter Index command

ALTER INDEX ALL ON TableName REBUILD

What is especially cool about 2005 is that you can choose to throttle the amount of CPUs you use for the defragging process, with the MAXDOP option note, with the following command you can restrict the number of processors down to 2 CPUs:

ALTER INDEX ALL ON TableName REBUILD WITH(MAXDOP=2)

You can also choose to rebuild your INDEX and continue to make it available for your users as it defrags, I suspect this has performance implications but none the less the command would be:

ALTER INDEX ALL ON TableName REBUILD WITH(MAXDOP=2, ONLINE=ON)

More information on the ALTER INDEX command is available on the MSDN here:
http://msdn2.microsoft.com/en-us/library/ms188388.aspx

Happy Defragging!

How is your Server Configured

2005-02-16T08:58:00.000-08:00

The following script works well on Sql Server 2000 (I have not tested it w/ any other versions). The purpose is to gain as much information back on how your sql server is configured (general settings memory etc.)

print 'Free Space'
print '----------'
exec master.dbo.xp_fixeddrives

print 'Memory'
print '------'
exec master.dbo.xp_msver

print 'Database Configuration'
print '----------------------'
exec master.dbo.sp_configure

print'DB Size'
print'-------'
exec master.dbo.sp_databases

print 'Linked Servers'
print '--------------'
exec master.dbo.sp_helplinkedsrvlogin

print 'Windows info'
print '------------'
exec master.dbo.sp_server_info

Join against Lists in a Column

2005-02-07T11:21:00.000-08:00

Now how about you want to select against that Table you now get when you push a list of values through it?

this Second part is very usefull tho you will have to edit the table name within when you change source tables... possibly a sproc????

Declare @CursorLess Table (PkID INT, ItemList INT)

--Non Cursor solution
DECLARE @NC Table (PkID INT, ItemList VARCHAR(2000)) --Cursor like holding bin
DECLARE @PkID AS INT, @ListOfItems As VARCHAR(2000) -- IDs pointing back to original table
INSERT INTO @NC (PkID, ListOfItems)
--Change the below select to the actual PKID and List of Items, and Table
Select PkID, ListOfItems From UserTable --Populate Holding bin
----------------------------------------------------
WHILE EXISTS(SELECT * FROM @NC) --Check that data exists
BEGIN
SELECT TOP 1 @PkID = PkID, @ListOfItems = ListOfItems FROM @NC --Set Variables
INSERT INTO @Cursorless (PkID, ListOfItems) SELECT @PkID, Field1 FROM dbo.udfListToTable(@ListOfItems, ';') -- Populate Temp Table
DELETE From @NC where PkID = @PkID --Clear Variables
END

SELECT * FROM @Cursorless -- Use the final Variable table to join approriately....

List to Table

2005-02-06T23:00:00.000-08:00

Every now and again, a developer will be faced with the task of referincing a List of id's. It's typically irresistable to want to push all these IDs into a single field. If you're searching then you may end up considering a cursor... for that I decided to write the following function... it's like other Tsql functions that push a list to a table, but this is my take on it... I release the code here with out warranty.

CREATE FUNCTION udfListToTable (@HList VarChar(2000), @Delimiter CHAR(1))
RETURNS @ListTable TABLE (Field1 VARCHAR(6))
AS
BEGIN
--By: Francisco Tapia
--Date: 2/1/2005
--Purpose: To convert a Comma delimited text to a Temp Variable table To help avoid dynamic sql
-- Instead you can join the temp table or use it in your where clause if a field is IN the subquery
DECLARE @FieldText as VarChar(6)

IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
SET @HList = @HList + @Delimiter

WHILE CHARINDEX(@Delimiter, @HList) > 0
BEGIN
IF CHARINDEX(@Delimiter, @HList) > 0
BEGIN
SELECT @FieldText =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
END
ELSE
BEGIN
SELECT @FieldText = RTRIM(LTRIM(@HList))
END
--Insert into Variable Table
INSERT INTO @ListTable(Field1)
SELECT RTRIM(LTRIM(@FieldText))
--Remove Item from list
SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) - CHARINDEX(@Delimiter, @HList))
END
RETURN
END