Friday 31 December 2010

2010

Back in January 2010, I set myself some goals.
I reviewed them in June and had made good progress. Now the year is over, it’s time to review the remainder....

Improve my web presence -

Tidy sqlsolace, improve tagging and searchability -

Achieved :
1. New template implemented giving site a fresh look and full screen width.
2. Reviewed some old posts
3. Categorised old content

Review (& either scrap or post) my backlog of scripts and notes -

Achieved : 50 posts from old notes

Review and remove old/unprofessional material from my personal web site

Achieved : Site Tidy up completed. Some sarcasm remains but nobody’s perfect...


Community -

Publish further scripts on other sites (September 2009 : had 1st published on SSC) -

Achieved : I have now had 7 scripts published @ SQLServerCentral . Not as many as I would like, but I’d rather they were useful than just fulfil a quota.

Publish an article

Achieved : SQL Server Club featured an exploration of understanding SQL Server Collations.


Attend Sql Server events / meetups

Achieved : 3
SQLBits VI in London, April 2010
Kent SQL User Group, August 2010
SQLBits VII in York, October 2010


Network more

Achieved : Connections on LinkedIn and Twitter with the UK Sql server community (mostly met through SQLBits)

Learning – 

Continue with SSIS development to further my ETL skills.

Achieved : 3 SSIS Solutions are now in production

Business Requirements and company direction have sadly prevented any new SSIS projects being commissioned. Scaling up SSIS remains a professional goal.

Obtain Microsoft BI certifications

Achieved : Passed MCTS (exam 70-448) and MCITP (exam 70-452) in SQL 2008 Business Intelligence in September 2010. They took a month of study , by which I mean I did nothing else with my evenings except study!

Having achieved this, I’m itching to get my teeth into an Analysis Services project.


Tackle the mountain of books, whitepapers and printed articles in my office.

Achieved :

None (in their entirety that is)
I have 3 books on the go, each of which I am half way through. These are -
  1. SQL Server MVP Deep Dives
  2. Inside Microsoft SQL Server 2008 T-SQL Querying
  3. Professional Sql 2008 Internals and Troubleshooting

Saturday 18 December 2010

Bookmark : Role of the Infrastructure DBA

A great post from Gavin Payne explaining how the DBA role has evolved.
I can certainly relate to this having spent a lot of the last 2 years running sql server on Hyper-V and having configured my own Windows 2008 Active Directory domain too.

Link : The Role of the Infrastructure DBA

Wednesday 15 December 2010

RETRY mechanism with WHILE loop (tsql template)

This code demonstrates a RETRY mechanism with a WHILE loop.
The loop continues until the task succeeds or @MaxAttempts is reached.
If @MaxAttempts is reached and the task still fails, an email is sent

BEGIN

SET NOCOUNT ON

DECLARE @MaxAttempts INT
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @ErrorMessage NVARCHAR(4000)  
DECLARE @ErrorSeverity INT  
DECLARE @EmailMessage VARCHAR(100)

SET @ErrorMessage = 'run_attempt'
SET @Counter = 0
SET @maxAttempts = 3


-- @ErrorMessage will be NULL after a successful execution of the program.
-- Checking for IS NOT NULL will mean the loop continues until success.

WHILE (@ErrorMessage IS NOT NULL) AND (@ErrorMessage NOT LIKE 'Warning: Null value%') AND (@Counter <= @MaxAttempts )

BEGIN   

 -- Increment counter and display run number to the screen
 SELECT @Counter = @Counter + 1 
 SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
 RAISERROR (@CounterText, 10, 1) WITH NOWAIT   
 
 
 BEGIN TRY
 
  -- Run the program you want to 'retry' 
  EXEC myschema.mystoredProc
  SELECT  @ErrorMessage = ERROR_MESSAGE() ,  @ErrorSeverity = ERROR_SEVERITY()  
 
 END TRY
 
 BEGIN CATCH

  IF @Counter = @MaxAttempts
  BEGIN
  
    SET @EmailMessage = 'mystoredProc  : Run Attempt : ' + CONVERT(VARCHAR(30),@Counter,23) + ' failed '

    EXEC msdb.dbo.sp_send_dbmail 
       @profile_name='SQL Profile'
     , @recipients='recipent@domain.com'
     , @body=@EmailMessage
     , @subject=@EmailMessage
     , @importance='High'
     
  END
 
 END CATCH

END  

END

Saturday 11 December 2010

Free Online Storage : Dropbox


Am really liking this tool, and am favouring it over my usb drive for smaller, more frequently accessed stuff.

Thursday 9 December 2010

Using MAXDOP to throttle an index build

CREATE UNIQUE CLUSTERED INDEX CI_MyIndex
ON Processiing.myPartitionedTable (DateTime, ID) WITH (MAXDOP=1,DATA_COMPRESSION = PAGE,ONLINE = ON)
ON PScheme_YearDateRange (DateTime) 
GO

Links :

MAXDOP WITH ALTER INDEX
http://www.sqldev.org/sql-server-database-engine/how-does-maxdop-work-with-alter-index-87328.shtml

MAXDOP
http://blogs.msdn.com/arali/archive/2009/11/26/sql-server-max-degree-of-parallelism-maxdop.aspx

Partitioning walkthrough
http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/

Bookmark : Burnout & Recovery

SQL Server MVP Gail Shaw blogs today on "burnout and recovery"

A refreshingly honest post that I can relate to. She reflects on what has happened and suggests what steps she should have taken.



Wednesday 8 December 2010

SSIS : The Lookup Component (bookmarks)

No point in reinventing the wheel, but some great reading on the Lookup component and some methods to adopt when using it.

SSIS 2008 lookup component
http://consultingblogs.emc.com/jamiethomson/archive/2007/11/16/Katmai_3A00_-SSIS_3A00_-Lookup-component-gets-a-makeover.aspx


Lookup Component Cache Modes
http://blogs.msdn.com/mattm/archive/2008/10/18/lookup-cache-modes.aspx

Using the SQL 2008 Lookup and Cache Transforms in a SQL Server Integration Services (SSIS) Package
http://www.mssqltips.com/tip.asp?tip=1511

SSIS : Incremental persistent cache updates
http://blogs.msdn.com/mattm/archive/2008/11/23/lookup-pattern-incremental-persistent-cache-updates.aspx

SSIS 2008 - Incrementally Updating the Lookup Cache File
http://agilebi.com/jwelch/2008/05/06/ssis-2008-incrementally-updating-the-lookup-cache-file/

Cascading Lookup
http://blogs.msdn.com/mattm/archive/2008/11/22/lookup-pattern-cascading.aspx

Lookup component - Ensure varchar columns are not padded
http://consultingblogs.emc.com/kristianwedberg/archive/2006/02/22/2955.aspx

Handling Early Arriving Facts in SQL Server Integration Services SSIS
http://www.mssqltips.com/tip.asp?tip=1446

Tuesday 7 December 2010

Logging Errors to SQL logs via RAISERROR WITH LOG

Logging errors to SQL logs via RAISERROR >

1) The Code -
RAISERROR('Test of custom error logging', 18, 1) WITH LOG

2) Management Studio Results

3) Corresponding entry in the SQL Server log


Expanding on this we can pass the genuine error message through, like this -
1) The Code -
BEGIN TRY
   BEGIN TRANSACTION 

 -- Do action that we want rolled back if an error occurs
 DELETE Person.Address WHERE AddressID = 1  

   COMMIT
END TRY

BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK

 DECLARE @ErrorMessage NVARCHAR(4000)
 DECLARE @ErrorSeverity INT

 SELECT
  @ErrorMessage = ERROR_MESSAGE(),
  @ErrorSeverity = ERROR_SEVERITY()

 RAISERROR(@ErrorMessage, @ErrorSeverity, 1) WITH LOG
 
END CATCH

2) The SQL log -




You can do a variety of Error logging methods here in this way -
1 ) send email
2 ) write to table
3 ) log to sql via RAISERROR (as abover)

You have access to the following error functions -

ERROR_NUMBER() - returns the number of the error.
ERROR_SEVERITY() - returns the severity.
ERROR_STATE() - returns the error state number.
ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() - returns the line number inside the routine that caused the error.
ERROR_MESSAGE() - returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.


link : http://technet.microsoft.com/en-us/library/ms175976.asp

Saturday 20 November 2010

Updating Statistics Manually

To update statistics manually -

exec sp_updatestats

Without parameters this updates all statistics in current database.

Run in all databases like this -

exec sp_MSForeachdb 'use [?];exec sp_updatestats'

Indexed views

sp_updatestats does not update indexed view stats

Friday 19 November 2010

SQL login passwords and Pwdcompare

I'll spare the details but after a a little investigation, the HASHBYTES function cannot be used to generate a SQL login password. There are 2 undocumented functions pwdencrypt and pwdcompare that Management Studio uses for this.

If you were really interested in how to form the passwords, then the login migration script here will give more insight.

Link : How to transfer the logins and the passwords between instances of SQL Server 2005/8

The use of pwdcompare can be demonstrated as follows -

If I was daft enough to use my cat's name as a password (I'm not), the following query would return the username I had used the password with.

select name from sys.syslogins where pwdcompare('coco', password) = 1


Links:
cannot engineer sql password hash using HASHBYTES, have to use the undocumented stored procedure 'pwdencrypt'
SQL Server undocumented password hashing builtins: pwdcompare and pwdencrypt

Thursday 18 November 2010

Bookmark : WhoIsActive 10.0 / WhoIsActiveUI

Adam Machanic’s FREE WhoIsActive script has reached version 10.0

Download : WhoIsActive 10.0

and SchemaSolutions have produced a FREE SSMS plugin for it, called WhoIsActiveUI .

Download : WhoIsActiveUI

r

Friday 12 November 2010

Bookmark : Project Lucy

Quest Software have launched Project Lucy to analyse SQL Profiler trace (.trc) files.

Get their opinion on your servers for free at https://www.projectlucy.com/

Tuesday 9 November 2010

Bookmark : Naming standardization scripts

Michael Søndergaard has written some excellent Naming standardization scripts that he has published on his site.

They cover the naming of Indexes , Check Constraints, Default Constraints and Foreign Keys and can potentially save you a lot of time.
This of course is only if you can agree a Naming convention with your colleagues...

r

dbautils.spFixIndexNaming
dbautils.spFixColumnCheckNaming
dbautils.spFixColumnDefaultNaming
dbautils.spFixForeignKeyNaming

Wednesday 3 November 2010

Missing IDs in an integer sequence (the gap problem)

I encountered a missing integer ID problem today and rather than reinventing the wheel found the following script to deal with it.

Finding (all the) gaps in an identity column (or any integer based column for that matter) using Sql 2005

I have added OPTION (MAXRECURSION 0) to the end of the SELECT statement so that the CTE can be called as many times as necessary.

declare @i int;
 SELECT @i = MAX(pkid) FROM t1;
 WITH tmp (gapId) AS (
   SELECT DISTINCT a.pkid + 1
   FROM t1 a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.pkid + 1)
   AND a.pkid < @i
   UNION ALL
   SELECT a.gapId + 1
   FROM tmp a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.gapId + 1)
   AND a.gapId < @i
 )
 SELECT gapId
 FROM tmp
 ORDER BY gapId
OPTION (MAXRECURSION 0) ;
A good article on other sequence generation scripts is Creating a Number (Sequentially incrementing values) table in T-SQL

Tuesday 2 November 2010

Monday 1 November 2010

Tool : CPU-Z

I just read a post of Brent Ozar’s about the SQL Server installation at Stackoverflow.com and how the power-saving feature of the Nehalem processor was causing some fun.

Anyway, he points to an excellent FREE tool, CPU-Z which is new to me.
As Brent's post details, compare the 'specification' of the processor with the 'core speed' it is achieving.

Simples...

Download CPU-Z : http://www.cpuid.com/

Friday 29 October 2010

Shrinking Datafile in steps

Shrinking a database is only desirable in migration operations e.g. restoring to an underpowered development server. If you find yourself doing it, the following link is rather useful.

The script repeatedly shrinks a data file in small increments.
This avoids long file operations and makes it easy to stop at any time.

SQLTeam.com : Shrink DB File by Increment to Target Free Space

From the script, this piece of sql is useful to show Data File Usage.

select
 [FileSizeMB] =
  convert(numeric(10,2),round(a.size/128.,2)),
 [UsedSpaceMB] =
  convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
 [UnusedSpaceMB] =
  convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
 [DBFileName] = a.name
from
 sysfiles a

link : dba 101 : shrinkfile

Friday 22 October 2010

Script : Mark all tables for recompilation

sp_recompile - Recompiles objects.

usage : exec sp_recompile objectname tablename

Use against tables, views, triggers or stored procedures.

If you run against a table or view, it marks all SPs that reference the object to be recompiled.

So, to mark all tables (and stored procedures etc) for recompilation -

exec sp_msforeachtable  @command1="print '?'",  @command2="sp_recompile '?'"

Technet : sp_recompile

Thursday 21 October 2010

Wednesday 20 October 2010

Script : Sql logins without a default database

How to find sql logins that do not have a default database set?

SELECT * FROM sys.server_principals 
WHERE [type] IN ('U', 'G', 'S','C') ]
AND default_database_name IS NULL

NB : Column TYPE_DESC provides the meaning of TYPE.

If a database has been deleted, a login could still have it's default database set to one that has been removed. To find these ...

SELECT * FROM sys.server_principals 
WHERE [type] IN ('U', 'G', 'S','C') 
AND [default_database_name] NOT IN (SELECT name FROM sys.databases)

Thursday 14 October 2010

Implicit column Conversions

I came across an excellent post ' Finding Implicit Column Conversions in the Plan Cache ' by Jonathan Kehayias today and had to try out his script to see if we had implicit conversions going on.
The only find was one of my most commonly used routines where I get the DATE portion from a DATETIME column.

I did do this -

SELECT CAST(DATEDIFF(D, 0, GETDATE()) AS DATETIME)

To prevent an implicit conversion occuring (ok, it's not exactly a massive overhead), I've changed the code to -

SELECT DATEADD(dd, DATEDIFF(dd, '1900-01-01', GETDATE()),'1900-01-01')


Further reading :



Wednesday 13 October 2010

Delete SQL Agent Job History

-- delete sql agent job history for named job

EXEC msdb.dbo.sp_purge_jobhistory @job_name = N'data load' ;


-- delete sql agent job history for named job, with date parameters

EXEC dbo.sp_purge_jobhistory  @job_name = N'data load', @oldest_date = '2008-12-31'
 

-- delete all sql agent job history

EXEC msdb.dbo.sp_purge_jobhistory

Sunday 3 October 2010

SQLBits VII

Having gone to Wales for SQLBits 5 and London for SQLBits 6, it was time for SQLBits to head north. York was the destination for this autumninal conference for UK SQL Server professionals with the University being the venue. Once again - the venue, organisation and grub was excellent.

The training day i attended is a post on it's own, but I'll list the Friday and Saturday sessions I attended here. I'm not going to review sessions individually, as
  1. i don't have time
  2. taste is subjective
  3. the internet is full enough of opinions
  4. others will do a better job
  5. the videos will end up on sqlbits.com and you can review them yourself.
Anyway, how I spent my SQLBits ...

Friday 1st October

Automating SSIS
Andre Kamman

SSIS Dataflow Performance Tuning
Jamie Thomson

TSQL Techniques - How and Why to tune a routine
Dave Ballantyne

Common Analysis Services mistakes and how to avoid them
Chris Webb

The Developer Side of the Microsoft Business Stack
Sascha Lorenz

Saturday 2nd October

Designing and Tuning High Speed Data Loading
Thomas Kejser

SQL Server Storage - 1,000GB level
Brent Ozar

Monitoring and Tuning Parallel Query Execution - Part II
Ramesh Meyyappan

Alice's Adventures in Reporting Services
Jennifer Stirrup

SSIS Field Notes
Darren Green

The content was great and I was once again left with wanting to try everything out. This was followed by the grim realisation that my projects would not allow me to. In the breaks and evenings it was fun to finally meet some of the bloggers and sql tweeps i've been following.

Notable mentions go to Gavin Payne (blog | twitter) , Jonathan Allen (blog | twitter) , John Sansom (blog | twitter) , Mark Blakey (blog | twitter) , Phil Nolan (blog | twitter) , Rachel Clements (blog | twitter) and a very modest bloke (and SSIS legend) Jamie Thomson (blog | twitter).


Update : 04 November 2010 -

I'm linking the best of the SQLBits 7 posts.
These people have reviewed their SQLBits experiences properly...

Ashley Burton - SQLBits 7 Friday Rundown , SQLBits 7 Saturday Rundown

Jonathan Kehayias - SQLBits 7 Wrap Up

Jonathan Allen - SQLBits 7 - The Disappointment (food for thought  - the cost of those that failed to turn up)

Niall Best - SQLBits 7

Richard Back - SQLBits 7 Conference Report

Wednesday 29 September 2010

NEWSID breaks Windows Server 2008 R2

NEWSID and Windows Server 2008 R2 do not play nicely together.

Fortunately, I found out using VMs hence simply just restored the hard drive.

Link : NEWSID breaks Windows Server 2008 R2

The solution is to use Sysprep...
  1. Navigate to \Windows\System32\Sysprep
  2. Run Sysprep.exe
  3. Use the 'Generalize' tick bo.
  4. When you restart, you'll get the install prompts for country, language etc...
Links :
Using SYSPREP.EXE to Change SID in Windows Server 2008 and Vista
Sysprep in Windows Server 2008 R2
Replacement for NewSID when working with Windows 7?

Tuesday 21 September 2010

How to disable Windows Error Reporting (WER)

1) Go to Start , Run , type 'gpedit.msc' (or launch from AD if doing for a domain)
2) Computer Configuration\Administrative Templates\System\Internet Communication Management\Internet Communication settings
3) Enable 'Turn off windows error reporting'
 
Links:
Jordan Bortz : Losing space on Drive C? Check your WER ReportQueue
Technet : Windows Error Reporting

Sunday 19 September 2010

MCITP : Business Intelligence Developer 2008

Passed the BI MCITP Exam today. Am very relieved as it was the toughest of all the exams.

Am now 3 x MCITP in SQL 2008 Administration, Development and Business Intelligence :)

Tuesday 14 September 2010

Bookmark : How is fill factor impacting my indexes?

David Levy writes about Fill Factor for TSQL Tuesday and provides a useful script too...

http://adventuresinsql.com/2010/09/how-is-fill-factor-impacting-my-indexes/

SSIS : OLEDB Error Codes

Some error messages I (ssis) generated today - 

-1071607116 : A rowset based on the SQL command was not returned by the OLE DB provider.

-1073450982 : component "Component Name" (1) failed the pre-execute phase and returned error code 0xC02092B4.

I had a strange sense of deja-vu and hence have to point myself back to this post to sort it!

Solace : SSIS, OLEDB and Stored Procedures

Friday 10 September 2010

MCTS : SQL Server 2008, Business Intelligence Development and Maintenance

Just a quick note to say I passed 70-448 : Microsoft SQL Server 2008, Business Intelligence Development and Maintenance.

Not looking forward to the MCITP mind, that will be tough..

r

Bookmark : SSMS Templates

Templates are an overlooked feature of Management Studio.
I must remember to use them rather than jumping for Help / Books Online / Google !

Can't remember the syntax? Use templates!


Write your own SSMS Templates

Hyper-V : VHD size optimization #2

Time to revise an old post based on recent experiences with Hyper-V.
I posted on how to optimize the size of VHDs, but I'm revising that now to include step 2 below >

1) Defragment the VHD (Defraggler was better than the Windows tool)
2) Use Precompact.exe to a "zero out" i.e. overwrite with zeros available blank space. *
3) Shut down the VM and Shrink the VHD from Hyper-V.

* Precompact.exe is found in precompactor.ISO which in turn you get from the Virtual PC 2007 installation (in C:\Program Files (x86)\Microsoft Virtual PC\Virtual Machine Additions)

Link : Compacting Hyper-V files

Wednesday 1 September 2010

Blatent Plug : SQLWorkshops.com - Free Performance Monitoring/Tuning Webcasts

There is a lot of good training material and video blogs for people wishing to further their SQL skills.
Pragmatic Works (for BI) , Cuppa Corner from SQLServerFAQ are my favourites, as well as community webcasts from Quest and Redgate.

Most of  the free stuff simply gets you going. It isn't rocket science, it just saves you an hour or so reading (another manual). Ramesh Meyyappan's SQLWorkshops site is different. 
The videos are still FREE , But they are 'Level 400' (from attending conferences I know this to mean 'the clever stuff'! ) Anyway, if you're interested in performance monitoring and tuning you can download them from http://www.sqlworkshops.com/webcast.
 
Ramesh Meyyappan attended SQLBits V where I saw him present his 'Let's make SQL fly' talk.
After presenting 'Monitoring & Tuning Parallel Query Execution' at SQLBits VI he is back for more on October 2nd to present 'Monitoring & Tuning Parallel Query Execution - Part II at SQLBits VII

* (yes I was encouraged to write this post due to a potential freebie t-shirt, but I do genuinely rate the webcasts)

rich

Windows Group Membership Checker

SQLServerCentral.com have published a script I use to look at Windows Group Membership. This is good if you use AD groups to manage security...

SSC : Windows Group Membership Checker



March 2011 Update : Putting the function here too now as SSC exclusivity period over...


/*
Script  : SQL Server - Windows Group Membership Checker
Version : 1.0 (August 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/

DECLARE @CurrentRow INT
DECLARE @TotalRows INT
SET @CurrentRow = 1

DECLARE  @SqlGroupMembership  TABLE(
    ACCOUNT_NAME      SYSNAME,
    ACCOUNT_TYPE      VARCHAR(30),
    ACCOUNT_PRIVILEGE VARCHAR(30),
    MAPPED_LOGIN_NAME SYSNAME,
    PERMISSION_PATH   SYSNAME
    )

DECLARE @WindowsGroupsOnServer TABLE(
   UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL 
 , Name  SYSNAME
 )
 
INSERT INTO @WindowsGroupsOnServer (NAME)
SELECT [NAME] FROM master.sys.server_principals WHERE TYPE = 'G' 

SELECT @TotalRows = MAX(UniqueRowID) FROM @WindowsGroupsOnServer

DECLARE @WindowsGroupName sysname 


-- Loop Each Windows Group present on the server
WHILE @CurrentRow <= @TotalRows 
   BEGIN 
  
  SELECT @WindowsGroupName  = [Name] 
  FROM @WindowsGroupsOnServer
  WHERE UniqueRowID = @CurrentRow 
  
    BEGIN TRY
    -- Insert found logins into table variable
    INSERT INTO @SqlGroupMembership (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH)
       EXEC xp_logininfo @WindowsGroupName , 'members' 
    END TRY

    BEGIN CATCH
    -- No action for if xp_logininfo fails
       END CATCH
       
 SELECT @CurrentRow = @CurrentRow + 1   
 
   END 
   
-- Display final results
SELECT  @@servername AS Servername
    , [PERMISSION_PATH] AS WindowsGroup
    , Account_Name
    , Mapped_Login_Name
    , Account_Type
    , Account_Privilege
FROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]



Wednesday 18 August 2010

An Indexed View story....

An Indexed View story.

Playing with INDEXED VIEWS on Sql 2008 Enterprise today, these are some facts I found. Fussy creatures, these Indexed views...

Attempt 1)
create index ix_testindex on [Report].[myView] (id)

Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'myView' because the view is not schema bound.

(I went back to the view definition and added WITH SCHEMABINDING )


Attempt 2)
create index ix_testindex on [Report].[myView] (id)

Msg 10140, Level 16, State 1, Line 1
Cannot create index on view 'dbtest.Report.myView' because the view contains a table hint. Consider removing the hint.
OK, you caught me out, I had a dirty (NOLOCK) hint in there. I recreated the view without this, and ...

Attempt 3)
create index ix_testindex on [Report].[myView] (id)

Msg 1940, Level 16, State 1, Line 1
Cannot create index on view 'Report.myView'. It does not have a unique clustered index.

Fair enough, I added 'UNIQUE CLUSTERED' to index creation script...

Attempt 4)
create unique clustered index ix_testindex on  [Report].[myView] (id) with (data_compression=page,maxdop=1,online=on) ON [indexes]

Msg 1967, Level 16, State 1, Line 1
Cannot create a new clustered index on a view online.

Argh! I cannot create the index online (an Enterprise feature if you didn't know).

Attempt 5)
create unique clustered index ix_testindex on [Report].[myView] (id) with (data_compression=page,maxdop=1,online=on) ON [indexes]

Phew! , this worked.
Note : I'm using named filegroups for indexes.

Thursday 12 August 2010

Remote Desktops SnapIn in Windows 7

Want the Remote Desktops SnapIn in Windows 7 ?

1) Download and Install the Remote Server Administration Tools for Windows 7

2) Control Panel, and then click Programs.

3) Programs and Features area> 'Turn Windows features on or off'

4) Windows Features  expand Remote Server Administration Tools, tick 'Remote Desktop Services Tools' >


5) Following a reboot, you'll find it under the start menu :) >


6) Right Click 'Remote Desktops' and select 'Add New Connection' to add each server.

Tuesday 3 August 2010

SQL Server & Network Packet Size

I've seen a number of references to packet size on my research into optimising SQL & SSIS.
The default packet size is 512 Bytes and the maximum SQL server supports is 32767.

So, the question is, " Why can't i just change it ? "

Hardware support is the answer
Connecting clients and the network switches need to support the packet size (e.g. 9000 for Jumbo frames)
Network Cards need to support the speed (and for that support to be enabled via the drivers in Windows)

Your OS also needs to support it. In the case of Hyper-V (pre Windows 2008 R2), the virtual NIC cannot support Jumbo frames.

If your environment is suitable however, here's the configuration code to do it >

EXEC SP_CONFIGURE 'show advanced option', '1'; 
RECONFIGURE;
EXEC SP_CONFIGURE;

EXEC SP_CONFIGURE 'network packet size (B)', '9000';
RECONFIGURE WITH OVERRIDE;
RECONFIGURE;

EXEC SP_CONFIGURE

Link : Gigabit Ethernet Jumbo Frames

Sunday 1 August 2010

Automatically Script Non Clustered Indexes

As part of a larger backup project I wanted to automatically script my non clustered indexes to a file and have set about writing a procedure to do this. Here are the steps to follow if you want to achieve the same.

1) Enable OLE automation

2) Create utils.usp_OLEwritefile (sp that Writes to a text file from TSQL)

3) Create utils.usp_GenerateIndexesScript as follows -

/* 
Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )

Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com

 1) Changed Schema of routine to Utils
 2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
 3) Added Schemas to script
 4) Reformatted for clarity
 5) Compression Option added with compression type
 6) Existing Index detection script changed to use sys.indexes and sys.objects
 7) Fix for Included Column Ordering
 
--  Usage: 

EXEC utils.usp_GenerateIndexesScript 
  @IncludeFileGroup   = 1
    ,@IncludeDrop    = 1
    ,@IncludeFillFactor   = 1
    ,@destinationparameter  = '\\SERVER-01\sqlbackup$\Indexes\myindexes.sql'
    ,@IncludeCompression  = 1    
    ,@compressionType   = 'PAGE'
*/ 
CREATE PROCEDURE [utils].[usp_GenerateIndexesScript]
(
     @IncludeFileGroup  bit = 1
    ,@IncludeDrop   bit = 1
    ,@IncludeFillFactor  bit = 1
    ,@destinationparameter  NVARCHAR(1000) = NULL
    ,@IncludeCompression bit = 1
    ,@compressionType  VARCHAR(4) = 'PAGE'
 
)
AS

BEGIN
    -- Get all existing indexes, but NOT the primary keys
    DECLARE Indexes_cursor CURSOR
        FOR SELECT 
     SC.Name   AS SchemaName
     , SO.Name  AS TableName
                    , SI.Object_Id  AS TableId
     , SI.[Name]     AS IndexName
     , SI.Index_ID   AS IndexId
     , FG.[Name]     AS FileGroupName
     , CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
              FROM sys.indexes SI
              LEFT JOIN sys.filegroups FG
                     ON SI.data_space_id = FG.data_space_id
              INNER JOIN sys.objects SO
     ON SI.object_id = SO.object_id
     INNER JOIN sys.schemas SC
     ON SC.schema_id = SO.schema_id
             WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1
               AND SI.[Name] IS NOT NULL
               AND SI.is_primary_key = 0
               AND SI.is_unique_constraint = 0
               AND IndexProperty(SI.Object_Id, SI.[Name], 'IsStatistics') = 0
               AND FG.[Name]  IS NOT NULL
             ORDER BY SC.Name , SO.Name ,SI.[Name]    

    DECLARE @SchemaName  sysname
    DECLARE @TableName  sysname
    DECLARE @TableId  int
    DECLARE @IndexName  sysname
    DECLARE @FileGroupName sysname
    DECLARE @IndexId  int
    DECLARE @FillFactor  int

    DECLARE @NewLine nvarchar(4000)     
    SET @NewLine = CHAR(13) + CHAR(10)
    
    DECLARE @Tab nvarchar(4000)     
    SET @Tab = Space(4)

 DECLARE @SQLOutput nvarchar(max) 
 SET @SQLOutput = ''
 
    -- Loop through all indexes
    OPEN Indexes_cursor

    FETCH NEXT
     FROM Indexes_cursor
     INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

    WHILE (@@Fetch_Status = 0)
        BEGIN

            DECLARE @sIndexDesc nvarchar(4000)
            DECLARE @sCreateSql nvarchar(4000)
            DECLARE @sDropSql  nvarchar(4000)

            SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @SchemaName + '.' + @TableName
            SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
                          + '            FROM sys.indexes si' + @NewLine
                          + '            INNER JOIN sys.objects so' + @NewLine
                          + '                   ON so.object_id = si.object_id' + @NewLine
                          + '           WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
                          + '             AND so.[Name] = N''' + @TableName + ''')  -- Table Name' + @NewLine
                          + 'BEGIN' + @NewLine
                          + '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
                          + 'END' + @NewLine

            SET @sCreateSql = 'CREATE '

            -- Check if the index is unique
            IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'UNIQUE '
                END
            --END IF
            -- Check if the index is clustered
            IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'CLUSTERED '
                END
            --END IF

            SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

            -- Get all columns of the index
            DECLARE IndexColumns_cursor CURSOR
                FOR SELECT SC.[Name],
                           IC.[is_included_column],
                           IC.is_descending_key
                      FROM sys.index_columns IC
                     INNER JOIN sys.columns SC
                             ON IC.Object_Id = SC.Object_Id
                            AND IC.Column_ID = SC.Column_ID
                     WHERE IC.Object_Id = @TableId
                       AND Index_ID = @IndexId
                     ORDER BY IC.[is_included_column],
         IC.key_ordinal

            DECLARE @IxColumn   sysname
            DECLARE @IxIncl     bit
            DECLARE @Desc     bit
            DECLARE @IxIsIncl     bit     SET @IxIsIncl = 0
            DECLARE @IxFirstColumn  bit     SET @IxFirstColumn = 1

            -- Loop through all columns of the index and append them to the CREATE statement
            OPEN IndexColumns_cursor
            FETCH NEXT
             FROM IndexColumns_cursor
             INTO @IxColumn, @IxIncl, @Desc

            WHILE (@@Fetch_Status = 0)
                BEGIN
                    IF (@IxFirstColumn = 1)
                        BEGIN
                            SET @IxFirstColumn = 0
                        END
                    ELSE
                        BEGIN
                            --check to see if it's an included column
                            IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                                BEGIN
                                    SET @IxIsIncl = 1
                                    SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ',' + @NewLine
                                END
                            --END IF
                        END
                    --END IF

                    SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
                    -- check if ASC or DESC
                    IF @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
                                END
                            --END IF
                        END
                    --END IF
                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END
            --END WHILE
            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

            IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (' 
                  END

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql  + 'FillFactor = ' + Cast(@FillFactor as varchar(13)) 
                END
     
                IF @IncludeCompression = 1
                BEGIN
        IF @IncludeFillFactor = 1
       BEGIN
        SET @sCreateSql = @sCreateSql + ','
       END
                    SET @sCreateSql = @sCreateSql  + 'DATA_COMPRESSION = ' + @compressionType
                END
                
                 IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql  + ')' + @NewLine
                END
                            
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine
                END
            --END IF

            PRINT '-- **********************************************************************'
            PRINT @sIndexDesc
            PRINT '-- **********************************************************************'


  SET @SQLOutput = @SQLOutput + '-- **********************************************************************' + @NewLine
        SET @SQLOutput = @SQLOutput +  @sIndexDesc + @NewLine
        SET @SQLOutput = @SQLOutput +  '-- **********************************************************************' + @NewLine
  SET @SQLOutput = @SQLOutput + @NewLine


            IF @IncludeDrop = 1
                BEGIN
                    PRINT @sDropSql
                    PRINT 'GO'
                    
                   SET @SQLOutput = @SQLOutput + @sDropSql + @NewLine
                   SET @SQLOutput = @SQLOutput + 'GO' + @NewLine 
                    
                END
            --END IF

            PRINT @sCreateSql
            PRINT 'GO' + @NewLine  + @NewLine
           
            SET @SQLOutput = @SQLOutput + @sCreateSql + @NewLine
            SET @SQLOutput = @SQLOutput + 'GO' + @NewLine  


            FETCH NEXT
             FROM Indexes_cursor
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END

    --END WHILE
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor
    
    -- @SQLOutput contains the output to place in a file
    

IF LEN(@destinationparameter) > 0
 BEGIN

 EXEC utils.usp_OLEwritefile  @FileName = @destinationparameter
        , @TextData = @SQLOutput
        , @FileAction = 'CREATENEW'
 END    
    
    --PRINT @SQLOutput
END


Run the procedure to script the indexes as follows -
The compression and destination file parameters are optional!

EXEC utils.usp_GenerateIndexesScript 
     @IncludeFileGroup   = 1
    ,@IncludeDrop   = 1
    ,@IncludeFillFactor   = 1
    ,@destinationparameter  = 'c:\MyNonClusteredIndexes.sql'
    ,@IncludeCompression  = 1    
    ,@compressionType   = 'PAGE'

Thursday 29 July 2010

Accessing a windows share from a Mac

Accessing a windows share from a Mac (ugh!)

smb://username@servername.fqdn/sharename

We were then prompted for the password, and presto!

Source : Apple Support - http://support.apple.com/kb/ht1568

Wednesday 28 July 2010

Backup all Filegroups

A short script I wrote to backup all filegroups has been featured on SQLServerCentral.com today.

SSC : Backup all Filegroups

Tuesday 27 July 2010

Bookmark : Security Tools

SQLBF - Brute force or dictionary password, from a hash.
http://www.cqure.net/wp/sqlpat/

SQLAT - SQL Auditing Tool
http://www.cqure.net/wp/sql-auditing-tools/

MSSQLScan - Find SQL Server instances
http://www.cqure.net/wp/mssqlscan/


* Links are supplied for information only. These are for testing your OWN servers, networks & passwords! *

Monday 26 July 2010

SQL 101 : Tempdb

This post explains exactly what tempdb is responsible for -

Iain Kick - Not another tempdb post

And what to do to tune it, namely -

1) RAID level (& separate version)
2) Instant File Initialization
3) Trace Flag T1118
4) Pre Size tempdb
5) Split tempdb per processor core

Friday 23 July 2010

Filegroup Backups

A quick runthrough experimenting with Filegroup Backups

BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' 
TO DISK = 'c:\Adventureworks.BAK'

Msg 3004, Level 16, State 1, Line 1
The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

So , to use this technique, you can't use SIMPLE recovery mode.
Why am i using SIMPLE? - It's a development box!
Setting to FULL resolves this -

ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' 
TO DISK = 'c:\Adventureworks.BAK'

Processed 21280 pages for database 'Adventureworks', file 'AdventureWorks_Data' on file 2.
Processed 1 pages for database 'Adventureworks', file 'AdventureWorks_Log' on file 2.
BACKUP DATABASE...FILE= successfully processed 21281 pages in 8.921 seconds (18.636 MB/sec).

Using the system table sys.filegroups, i can build a list of backup commands to backup all filegroups, like this ...


DECLARE @backupPath VARCHAR(500)
SET @backupPath = 'd:\sqlbackups\' 

DECLARE @backuptimestamp VARCHAR(30)
SET @backuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')

SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @backupPath + @backuptimestamp + '_' + DB_NAME() + '_' + name +'.BAK''' + ' WITH COMPRESSION ' AS BACKUPCOMMAND
FROM sys.filegroups
 
NB : My commands feature the compression setting as am on SQL 2008 Enterprise.

Thursday 22 July 2010

Bookmark : SQL Server DMV Starter Pack E-Book Published

The SQL Server DMV Starter Pack is a download comprising of an E-Book & Scripts for 28 DMV based queries. It has been put together by Glenn Berry, Louis Davidson and Tim Ford and is available as another freebie from Redgate

The 28 queries it provides, are -

Connections, Sessions, Requests, Queries

1: Are you Connected?
2: Session Ownership
3: Current expensive, or blocked, requests
4: Query Stats – Find the "top X" most expensive cached queries
5: How many single-use ad-hoc Plans?
6: Ad-hoc queries and the plan cache
7: Investigate expensive cached stored procedures
8: Find Queries that are waiting, or have waited, for a Memory Grant

Transactions

9: Monitor long-running transactions
10: Identify locking and blocking issues

Databases and Indexes

11: Find Missing Indexes
12: Interrogate Index Usage
13: Table Storage Stats (Pages and Row Counts)
14: Monitor TempDB

Disk I/O

15: Investigate Disk Bottlenecks via I/O Stalls
16: Investigate Disk Bottlenecks via Pending I/O

Operating System

17: Why are we Waiting?
18: Expose Performance Counters
19: Basic CPU Configuration
20: CPU Utilization History
21: Monitor Schedule activity
22: System-wide Memory Usage
23: Detect Memory Pressure
24: Investigate Memory Usage Across all Caches
25: Investigate memory use in the Buffer Pool

Other Useful DMVs

26: Rooting out Unruly CLR Tasks
27: Full Text Search
28: Page Repair attempts in Database Mirroring

Redgate : Download SQL Server DMV Starter Pack

Wednesday 21 July 2010

Windows 2008 : Hyper-V - Disappearing Client for Microsoft Networks

Two years ago, i posted about a networking eccentricity with Hyper-V where the 'Client for Microsoft Networks' would disappear from the clients.....


On inspecting the Local Area Connection Properties, ‘Client for Microsoft Networks’ is unticked.  Simply clicking the tickbox appears to work fine, but when you click OK , the following appears-
" Your current selection will also disable the following features: Client for Microsoft Networks "
" Are you sure you want to disable these feature(s)? "

An easier way to resolve this is as follows >

1) Go to Network Connections -




2) Press ALT to display the menu bar and select 'Advanced Settings' from the revealed 'Advanced' menu.



3) Select the virtual NIC (Local Area Conection 4 in my screenshot). Tick the Client for Microsoft Networks check box and the 2 boxes under it for IPv4 and IPv6.


MSDN Forum

Tuesday 20 July 2010

SSIS , OLEDB and Stored Procedures

SSIS , OLEDB and Stored Procedures.

SSIS cannot 'see' the metadata for returned data from stored procedures like it can for tables and views. Here is a collection of methods to try when using sprocs with SSIS.

On oledb connection managers, set -

DelayValidation = True

On data sources and components inside the data flow, set -

ValidateExternalMetaData = False

When calling your stored procedure, prefix as follows -

"SET FMTONLY OFF; EXEC dbo.myprocedure @param = 1"

In the stored procedure itself, create a header as follows -

CREATE PROCEDURE dbo.myprocedure (@param INT)
AS
BEGIN

-- {options to set...}
SET NOCOUNT ON; -- {supress 'rows affected' messages}

SET FMTONLY OFF; -- {ensure full data retuned. incase FMTONLY ON was in effect}


-- {false header to pass metadata}

IF 1 = 0
 BEGIN
 SELECT
   CAST(NULL AS INT) AS [intColumn1]
 , CAST(NULL AS INT) AS [intColumn2]
 , CAST(NULL AS VARCHAR(5)) AS [varcharColumn1] 
 , CAST(NULL AS VARCHAR(255)) AS [varcharColumn2] 
 , CAST(NULL AS DATETIME2(0)) AS [datetime2Column1]
     , CAST(NULL AS VARBINARY(20)) AS [varbinaryColumn1]  
 END

-- {genuine query}

SELECT intColumn1, intColumn2, varcharcolumn1 ...

END
GO


SSISTalk: Phil Brammer - Stored Procedures and the OLE DB Source
ReplicationAnswers : Coping with no column names in the oledb source

Saturday 17 July 2010

SQL Server Processor Configuration

AFFINITY controls processors and threads used by SQL server,
MAXDOP controls the maximum number of processors a single query can use.

solace : Affinity & Affinity I/O
solace : Max Degree of Parallelism (MAXDOP)

Friday 16 July 2010

SQL 2008 on Windows 2008 R2 / Windows 7

Quick notes :

To install SQL 2008 on Windows 2008 R2 you must install the application server role first!

To successfully run SQL 2008 on Windows 7, make sure you install Service Pack 1,

Andrew Fryer's blog : SQL Server 2008 on Windows 7 / Windows server 2008 r2
Softpedia : Make Windows 7 Play Nice with SQL Server 2008
Joes Barreto : Changes in Roles, Role Services and Features from Windows 2008 to Windows 2008 R2

Wednesday 14 July 2010

Bookmark : Troubleshooting SSPI errors : Detecting a bad SPN

'Advanced Troubleshooting Week at SQL University, Lesson 1' caught my eye today.
Not because of it's catchy title, because of the content (which i could have done with 2 years ago).

It's on SPNs (service principle names) those Active Directory entries needed for Kerberos authentication and I heartily recommend it.

Bookmarking here, so I find it again....

SSC: Troubleshooting SSPI errors - Detecting a bad SPN

Bookmark : Error Line Numbers

I have always found stored proc error line numbers in Management Studio somewhat confusing.
They never match what you see on screen!

The link below explains it fully, but in short -

"its the line in the original batch that compiled the procedure" so, "it includes the comments and the white space before it"...

Link :  What line does the error line number refer to?

Tuesday 13 July 2010

Bookmark : How do you learn BI as a DBA?

Johnathan Kehayias has published something very much at the forefront of my thoughts right now, an article entitled How do you learn BI as a DBA? 

Having earned my MCITP Database Administrator 2008 and MCITP Database Developer 2008 last year, I'm keen both to certify in BI , expand my SSIS development work and get to grips with SSAS development.

The following articles are referenced in Jonathan's post and will serve as my starting point too.

Planning Your First Microsoft BI Solution
Building a Data Foundation for a BI Solution
Building Your First Cube

Monday 12 July 2010

Scripting Data to a .CSV format

A simple way to script example data, so it can be posted in forums etc...

SELECT  'SELECT '
      + QUOTENAME(column1,'''')+','
      + QUOTENAME(column2,'''')+','
      + QUOTENAME(column3,'''')
      + ' UNION ALL'
 FROM myschema.mytable

SSC : How to post data/code on a forum

Sunday 11 July 2010

SQL Server Default Trace

SQL Server Default Trace. Useful to tell who did what and when!

-- Get info about the default trace (includes the trace file location) ...
SELECT * FROM ::fn_trace_getinfo(default)

-- Show everything you can get from querying the default trace ...
SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description
FROM ::fn_trace_geteventinfo(1) t
INNER JOIN sys.trace_events e ON t.eventID = e.trace_event_id
INNER JOIN sys.trace_columns c ON t.columnid = c.trace_column_id

-- Query the default trace ...
SELECT 
loginname,
loginsid,
spid,
hostname,
applicationname,
servername,
databasename,
objectName,
e.category_id,
cat.name,
textdata,
starttime,
endtime,
duration,
eventclass,
eventsubclass,
e.name as EventName
FROM ::fn_trace_gettable('D:\Data\MSSQL10.MSSQLSERVER\MSSQL\Log\log_152.trc',0)
INNER JOIN sys.trace_events e ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id


Links :
There's Something about SQL! : Default Trace
MSDN : Default Trace

Thursday 8 July 2010

Creating a 'System' Stored Procedure

Want to create a procedure that is available in any database (and will run in the context of that database) ?
  1. Create the procedure in the master database
  2. Prefix the name with sp_

USE master
GO

CREATE PROCEDURE sp_myproc AS
BEGIN
SELECT DB_NAME() AS CurrentDatabase
END
GO

USE tempdb
GO

EXEC sp_myproc
GO

Are my statistics up to date?

SELECT
   s.name   AS SchemaName
 , t.name   AS TableName
 , i.name   AS IndexName
 , i.type_desc AS IndexType
 , STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
FROM    sys.objects t  
INNER JOIN sys.indexes i  ON i.[object_id] = t.[object_id]
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] 
WHERE i.name IS NOT NULL AND s.name <> 'sys'
ORDER BY STATS_DATE(i.[object_id], i.index_id)  , t.name, i.type 

Wednesday 7 July 2010

Long Running Queries

A colleague sent me this to show what is currently running.
Look at the WHERE clause to adjust it for the duration threshold you are interested in.

SELECT
    r.session_id
  , p.kpid
  , r.start_time
  , DATEDIFF(SECOND, r.start_time, GETDATE()) as elapsed_time
  , st.text
  , r.status
  , r.command
  , r.cpu_time
  , r.wait_type
  , DB_NAME(r.database_id)
  , p.hostname
  , qp.query_plan
FROM
    sys.dm_exec_requests AS r
    INNER JOIN sys.sysprocesses AS p on r.session_id = p.spid
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE session_id > 50
AND DATEDIFF(SECOND, r.start_time, GETDATE())  > 10 -- duration in seconds
ORDER BY r.start_time


From Measure TSQL Statement Performance , this query provides performance statistics for cached query plans.

SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

Thursday 1 July 2010

Fast Delete !

Performance wise, a very fast delete can be achieved using a VIEW utilising an ORDER BY clause!!!

Create the view -
CREATE VIEW [dbo].[del_data] as
SELECT TOP(500) * FROM dbo.data WHERE id < 219150348 ORDER BY id
GO

Run a while loop to delete the data effeciently in batches -
WHILE(1=1)
BEGIN
DELETE dbo.del_data
IF @@ROWCOUNT < 500 BREAK
END

Link : SQLCAT - Fast ordered delete

Tuesday 22 June 2010

Generate Indexes Script - usp_GenerateIndexesScript

This is a procedure that generates scripts for your indexes.
Yes, you can do that by a few right click actions in Management Studio, but what if you need to automate it?
Included in this version-
  • File output - needs OLE
  • File Groups
  • Drop Statements
  • Fill Factor
  • Compression
  • Table Schemas

    IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA 
    WHERE SCHEMA_NAME = 'utils'
    AND SCHEMA_OWNER = 'dbo')
    BEGIN
        EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
    END
    GO
    
    IF NOT EXISTS(SELECT 1
                    FROM INFORMATION_SCHEMA.ROUTINES
                   WHERE ROUTINE_NAME = 'usp_GenerateIndexesScript'
                     AND ROUTINE_TYPE = N'PROCEDURE')
    BEGIN
        EXEC ('CREATE PROCEDURE [utils].[usp_GenerateIndexesScript] AS BEGIN SELECT 1 END')
    END
    GO
    
    /* 
    Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )
    
    Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com
    
     1) Changed Schema of routine to Utils
     2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
     3) Added Schemas to script
     4) Reformatted for clarity
     5) Compression Option added
    
    --  Usage: 
    
    EXEC utils.usp_GenerateIndexesScript 
      @IncludeFileGroup    = 1
        ,@IncludeDrop      = 1
        ,@IncludeFillFactor    = 1
        ,@IncludeCompression  = 1    
        ,@destinationparameter  = '\\Server\sqlbackup$\Indexes\'
    */ 
    
    IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA 
    WHERE SCHEMA_NAME = 'utils'
    AND SCHEMA_OWNER = 'dbo')
    BEGIN
        EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
    END
    GO
    
    IF NOT EXISTS(SELECT 1
                    FROM INFORMATION_SCHEMA.ROUTINES
                   WHERE ROUTINE_NAME = 'GenerateIndexesScript'
                     AND ROUTINE_TYPE = N'PROCEDURE')
    BEGIN
        EXEC ('CREATE PROCEDURE [utils].[GenerateIndexesScript] AS BEGIN SELECT 1 END')
    END
    GO
    
    ALTER PROCEDURE utils.usp_GenerateIndexesScript
    (
         @IncludeFileGroup   bit = 1
        ,@IncludeDrop     bit = 1
        ,@IncludeFillFactor   bit = 1
        ,@IncludeCompression bit = 1
        ,@destinationparameter NVARCHAR(1000) = NULL
     
    )
    AS
    
    BEGIN
        -- Get all existing indexes, but NOT the primary keys
        DECLARE Indexes_cursor CURSOR
            FOR SELECT 
         SC.Name   AS SchemaName
         , SO.Name   AS TableName
                        , SI.Object_Id     AS TableId
         , SI.[Name]         AS IndexName
         , SI.Index_ID       AS IndexId
         , FG.[Name]       AS FileGroupName
         , CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
                  FROM sys.indexes SI
                  LEFT JOIN sys.filegroups FG
                         ON SI.data_space_id = FG.data_space_id
                  INNER JOIN sys.objects SO
         ON SI.object_id = SO.object_id
         INNER JOIN sys.schemas SC
         ON SC.schema_id = SO.schema_id
                 WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1
                   AND SI.[Name] IS NOT NULL
                   AND SI.is_primary_key = 0
                   AND SI.is_unique_constraint = 0
                   AND IndexProperty(SI.Object_Id, SI.[Name], 'IsStatistics') = 0
                   AND FG.[Name]  IS NOT NULL
                 ORDER BY Object_name(SI.Object_Id), SI.Index_ID
    
        DECLARE @SchemaName  sysname
        DECLARE @TableName   sysname
        DECLARE @TableId     int
        DECLARE @IndexName    sysname
        DECLARE @FileGroupName sysname
        DECLARE @IndexId     int
        DECLARE @FillFactor    int
    
        DECLARE @NewLine nvarchar(4000)     SET @NewLine = CHAR(13) + CHAR(10)
        DECLARE @Tab   nvarchar(4000)     SET @Tab = Space(4)
    
     DECLARE @SQLOutput nvarchar(max) SET @SQLOutput = ' '
     
        -- Loop through all indexes
        OPEN Indexes_cursor
    
        FETCH NEXT
         FROM Indexes_cursor
         INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
    
        WHILE (@@Fetch_Status = 0)
            BEGIN
    
                DECLARE @sIndexDesc nvarchar(4000)
                DECLARE @sCreateSql nvarchar(4000)
                DECLARE @sDropSql  nvarchar(4000)
    
                SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName
                SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
                              + '            FROM sysindexes si' + @NewLine
                              + '            INNER JOIN sysobjects so' + @NewLine
                              + '                   ON so.id = si.id' + @NewLine
                              + '           WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
                              + '             AND so.[Name] = N''' + @TableName + ''')  -- Table Name' + @NewLine
                              + 'BEGIN' + @NewLine
                              + '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
                              + 'END' + @NewLine
    
                SET @sCreateSql = 'CREATE '
    
                -- Check if the index is unique
                IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
                    BEGIN
                        SET @sCreateSql = @sCreateSql + 'UNIQUE '
                    END
                --END IF
                -- Check if the index is clustered
                IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                    BEGIN
                        SET @sCreateSql = @sCreateSql + 'CLUSTERED '
                    END
                --END IF
    
                SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine
    
                -- Get all columns of the index
                DECLARE IndexColumns_cursor CURSOR
                    FOR SELECT SC.[Name],
                               IC.[is_included_column],
                               IC.is_descending_key
                          FROM sys.index_columns IC
                         INNER JOIN sys.columns SC
                                 ON IC.Object_Id = SC.Object_Id
                                AND IC.Column_ID = SC.Column_ID
                         WHERE IC.Object_Id = @TableId
                           AND Index_ID = @IndexId
                         ORDER BY IC.key_ordinal
    
                DECLARE @IxColumn   sysname
                DECLARE @IxIncl     bit
                DECLARE @Desc     bit
                DECLARE @IxIsIncl     bit     SET @IxIsIncl = 0
                DECLARE @IxFirstColumn  bit     SET @IxFirstColumn = 1
    
                -- Loop through all columns of the index and append them to the CREATE statement
                OPEN IndexColumns_cursor
                FETCH NEXT
                 FROM IndexColumns_cursor
                 INTO @IxColumn, @IxIncl, @Desc
    
                WHILE (@@Fetch_Status = 0)
                    BEGIN
                        IF (@IxFirstColumn = 1)
                            BEGIN
                                SET @IxFirstColumn = 0
                            END
                        ELSE
                            BEGIN
                                --check to see if it's an included column
                                IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                                    BEGIN
                                        SET @IxIsIncl = 1
                                        SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
                                    END
                                ELSE
                                    BEGIN
                                        SET @sCreateSql = @sCreateSql + ',' + @NewLine
                                    END
                                --END IF
                            END
                        --END IF
    
                        SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
                        -- check if ASC or DESC
                        IF @IxIsIncl = 0
                            BEGIN
                                IF @Desc = 1
                                    BEGIN
                                        SET @sCreateSql = @sCreateSql + ' DESC'
                                    END
                                ELSE
                                    BEGIN
                                        SET @sCreateSql = @sCreateSql + ' ASC'
                                    END
                                --END IF
                            END
                        --END IF
                        FETCH NEXT
                         FROM IndexColumns_cursor
                         INTO @IxColumn, @IxIncl, @Desc
                    END
                --END WHILE
                CLOSE IndexColumns_cursor
                DEALLOCATE IndexColumns_cursor
    
                SET @sCreateSql = @sCreateSql + @NewLine + ') '
    
                IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                    BEGIN
                        SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (' 
                      END
    
                IF @IncludeFillFactor = 1
                    BEGIN
                        SET @sCreateSql = @sCreateSql  + 'FillFactor = ' + Cast(@FillFactor as varchar(13)) 
                    END
         
                    IF @IncludeCompression = 1
                    BEGIN
            IF @IncludeFillFactor = 1
           BEGIN
            SET @sCreateSql = @sCreateSql + ','
           END
                        SET @sCreateSql = @sCreateSql  + 'DATA_COMPRESSION = PAGE'
                    END
                    
                     IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                    BEGIN
                        SET @sCreateSql = @sCreateSql  + ')' + @NewLine
                    END
                                
                --END IF
    
                IF @IncludeFileGroup = 1
                    BEGIN
                        SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                    END
                ELSE
                    BEGIN
                        SET @sCreateSql = @sCreateSql + @NewLine
                    END
                --END IF
    
                PRINT '-- **********************************************************************'
                PRINT @sIndexDesc
                PRINT '-- **********************************************************************'
    
    
      SET @SQLOutput = @SQLOutput + '-- **********************************************************************' + @NewLine
            SET @SQLOutput = @SQLOutput +  @sIndexDesc + @NewLine
            SET @SQLOutput = @SQLOutput +  '-- **********************************************************************' + @NewLine
      SET @SQLOutput = @SQLOutput + @NewLine
    
    
                IF @IncludeDrop = 1
                    BEGIN
                        PRINT @sDropSql
                        PRINT 'GO'
                        
                       SET @SQLOutput = @SQLOutput + @sDropSql + @NewLine
                       SET @SQLOutput = @SQLOutput + 'GO' + @NewLine 
                        
                    END
                --END IF
    
                PRINT @sCreateSql
                PRINT 'GO' + @NewLine  + @NewLine
               
                SET @SQLOutput = @SQLOutput + @sCreateSql + @NewLine
                SET @SQLOutput = @SQLOutput + 'GO' + @NewLine  
    
    
                FETCH NEXT
                 FROM Indexes_cursor
                 INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
            END
    
        --END WHILE
        CLOSE Indexes_cursor
        DEALLOCATE Indexes_cursor
        
        -- @SQLOutput contains the output to place in a file
        
    
    IF LEN(@destinationparameter) > 0
      BEGIN
      DECLARE @destinationfile varchar(500)
      DECLARE @destinationpath NVARCHAR(1000)
      DECLARE @as_at DATETIME
      DECLARE @databasename VARCHAR(100)
      DECLARE @servername VARCHAR(100)
    
      SET @servername = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(100))
      SET @databasename = CAST(DB_NAME() AS VARCHAR(100))
      SET @as_at = GETDATE()
    
      SET @destinationpath = @destinationparameter + @servername
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
      SET @destinationpath = @destinationpath + N'\' + @databasename
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
      SET @destinationpath = @destinationpath + N'\' + REPLACE(REPLACE(CONVERT(NVARCHAR(20),@as_at,120),' ','.'),':','')
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
    
      SET @destinationfile = @destinationpath + '\' + @databasename + '_Indexes_'+ REPLACE(REPLACE(CONVERT(NVARCHAR(20),@as_at,120),' ','.'),':','') + '.sql'
    
      EXEC sql_tools.utils.usp_OLEwritefile  @FileName = @destinationfile
            , @TextData = @SQLOutput
            , @FileAction = 'CREATENEW'
    END    
        
        --PRINT @SQLOutput
    END
    
    GO
    

    You execute the procedure like this -
    EXEC sql_tools.utils.usp_GenerateIndexesScript 
    @IncludeFileGroup  = 1
        ,@IncludeDrop = 1
        ,@IncludeFillFactor = 1
        ,@IncludeCompression  = 1    
        ,@destinationparameter = '\\SERVER07\sqlbackup$\Indexes\'
        
    GO