Wednesday, December 02, 2009

Rename a Sql Server Database

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

Tuesday, August 11, 2009

Boosting Performance

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,
Pages INT,
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))



INSERT INTO #db_defrag
EXEC (@sql)

DECLARE @TableName varchar(255)

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

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

FETCH NEXT FROM TableCursor INTO @TableName

CLOSE TableCursor


DROP TABLE #db_defrag

Tuesday, July 21, 2009

UTO! Unidentified Table Object

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

Tuesday, June 23, 2009

Mail Call!

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

Thursday, May 21, 2009

Limit your responses , please.

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

Thursday, April 09, 2009

MSDE enable TCP/IP or Named Pipes

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.

  1. In Windows, click Start and Run.

  2. Enter svrnetcn and click OK.

  3. Under the General tab, verify that the correct instance for the server is displayed in the Instance(s) on this server box.
  4. Highlight your desired protocol and click Enable (double clicking the name also moves the protocol to the enabled protocols box).
  5. Click OK.
  6. Restart the Sql Server Instance
  7. In Windows, click Start and Run.
  8. enter services.msc
  9. 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

Tuesday, March 17, 2009


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

Take the post about finding columns and incorporate it with this post from a college of mine.

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

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 AS TableName, AS SchemaName, AS ColumnName,
c.column_id AS ColumnID,
c.precision AS [Precision], AS TypeName, AS BaseTypeName, AS TypeSchemaName,
CASE WHEN c.max_length>=0 AND 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, AS DefaultName,
c.default_object_id AS defaultid, AS DefaultSchemaName,
CONVERT(bit, CASE ds.parent_object_id WHEN 0 THEN 1 ELSE 0 END) AS IsBoundDefault, AS RuleName, 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, AS FullTextTypeColumn,
c.is_xml_document AS XMLDocument, AS XMLCollection, 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

Friday, February 13, 2009

Turbo Charge Your SSMS

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!