SQL Server Performance

Add SPROC for each db in SQL Server

Discussion in 'General Developer Questions' started by bubberz, Aug 7, 2006.

  1. bubberz New Member

    Hello!

    I have about 15 databases on our instance of SQL Server, a and it's only growing. I'm trying to figure out how to run a stored procedure for each database without having to run it for the first in Query Analyzer, then select from the dropdown list at the top the next db, click "Execute", then select the next name....and so on. Seems like there's gotta be a better way. I'm searching Books Online, and haven't found anything yet.

    Thanks!

  2. FrankKalis Moderator

    If this is a one-time action, I would have a look at the undocumented sp_MSforeachdb procedure. You'll find tons of examples about that here and on the web.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. SQL_Guess New Member

    If you wanted to ensure that a SP is deployed to each DB WHEN THE DB IS CREATED you could add the SP to the Model database. The model DB is used when creating databases, and anything in it should be created in all of the databases. (That's the theorey - I've never had to use it).

    Alternatively, you could deploy the sp in master, so you only have 1 piece of code to support/re-deploy. This may be one of those rare cases where creating an SP in the master DB, and naming it sp_ may be a good idea, since a sp_ stored procedure can be called from any database on the instance.

    Finally, and again this is typically not a good practice, you could make use of the UNDOCUMENTED ms_ForEachDB stored procedure to run something for each database (UNDOCUMENTED SP = UNSUPPORTED, if you broke it it's your baby). Alternatively, you can do what the SP does, and create yourself a cursor that goes through all the DB you want it to, EXECing the SP you need. This is where that sp_ stored procedure from master would be very iseful, if it is possible.

    HTH

    Panic, Chaos, Disorder ... my work here is done --unknown
  4. bubberz New Member

    thanks for the replies!

    yes, as I keep adding new SPROCS to existing DBs, I keep a SQL script as well and add it there for any newly created dbs.

    I'll take a look @ sp_MSforeachdb......thanks for the help SQL Quess and FrankKalis!
  5. SQL_Guess New Member

    quote:Originally posted by bubberz

    thanks for the replies!

    yes, as I keep adding new SPROCS to existing DBs, I keep a SQL script as well and add it there for any newly created dbs.

    .. in that case, as suggested, look at deploying SP to the Model DB, or deploying the SP to master as an SP_ stored procedure.


    quote:Originally posted by bubberz
    I'll take a look @ sp_MSforeachdb......thanks for the help SQL Quess and FrankKalis!


    imho, rather do the cursor yourself, since (a) this is what ms_foreachdb does; (b) you can reduce the set of DB's you want to run against by coding the SELECT yourself, rather than having to run through all DB's ; (c) you avoid using undocumented MS feature that may change in operation during an hot-fix, service pack or new release (i,.e. 2005).

    Panic, Chaos, Disorder ... my work here is done --unknown
  6. SQL_Guess New Member

    Here's a sample for you:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />USE MASTER<br /><br />IF OBJECT_ID ('dbo.SP_ThisIsMyGlobalSP') IS NOT NULL<br />DROP PROCEDURE dbo.SP_ThisIsMyGlobalSP<br />GO<br />CREATE PROCEDURE [dbo].[SP_ThisIsMyGlobalSP]<br />@InputParametervarchar(25) <br />AS <br />BEGIN --proc<br />SET nocount on<br />SELECT 'Running with DB: ['+isnull(DB_NAME(),'DBNAME')+'] received parameter ['+isnull(@InputParameter,'parameter')+']'<br />END --proc<br />GO<br /><br />SET NOCOUNT ON<br />DECLARE @DBNamesysname <br />DECLARE@SQLCmdvarchar(250) <br /><br />DECLARE MyDBCursor CURSOR FOR SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'Report%' --note the ability to specify your DB's here<br />OPEN MyDBCursor<br />FETCH NEXT FROM MyDBCursor INTO @DBName<br />WHILE @@Fetch_status = 0 BEGIN<br />SET @SQLCmd = 'Use '+@DBName+' ;<br />EXEC SP_ThisIsMyGlobalSP ''DB execution'''<br />--SELECT @SQLCmd<br />exec (@SQLCmd)<br />FETCH NEXT FROM MyDBCursor INTO @DBName<br />END<br />CLOSE MyDBCursor<br />DEALLOCATE MyDBCursor<br /></font id="code"></pre id="code"><br /><br />Yes, cursors are bad, but MS_ForeEachDB is running a cursor anyway.<br />... this is the code behind the SQL2005 sp_MsForEachDB stored procedure:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />/*<br /> * The following table definition will be created by SQLDMO at start of each connection.<br /> * We don't create it here temporarily because we need it in Exec() or upgrade won't work.<br /> */<br /><br />create proc sys.sp_MSforeachdb<br />@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,<br />@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null<br />as<br /> set deadlock_priority low<br /> <br />/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */<br />/* @precommand and @postcommand may be used to force a single result set via a temp table. */<br /><br />/* Preprocessor won't replace within quotes so have to use str(). */<br />declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)<br />select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))<br />select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))<br />select @dbinaccessible = N'0x80000000'/* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */<br /><br />if (@precommand is not null)<br />exec(@precommand)<br /><br />declare @origdb nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />select @origdb = db_name()<br /><br />/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */<br /> /* Create the select */<br />exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' + --note the cursor here<br />N' where (d.status & ' + @inaccessible + N' = 0)' +<br />N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' )<br /><br />declare @retval int<br />select @retval = @@error<br />if (@retval = 0)<br />exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1<br /><br />if (@retval = 0 and @postcommand is not null)<br />exec(@postcommand)<br /><br /> declare @tempdb nvarchar(25<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /> SELECT @tempdb = REPLACE(@origdb, N']', N']]')<br /> exec (N'use ' + N'[' + @tempdb + N']')<br /><br />return @retval<br /><br /></font id="code"></pre id="code"><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  7. Roji. P. Thomas New Member

    I'd rather create an sp in the master database and mark it as a system proc so that I can run it from all the databases.



    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  8. SQL_Guess New Member

    Roji,correct me if I'm wrong, but you don't need to mark the SP as a system proc. simply creating it named sp_ will allow you to invoke it from the DB 's (per my example). Would you say that marking the SP as system is better for some reason?

    Panic, Chaos, Disorder ... my work here is done --unknown
  9. Roji. P. Thomas New Member

    If you expect the procedure to work as a local proc in every database (means the proc can refer the tables in the current database), you should mark it as a system Proc.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  10. SQL_Guess New Member

    quote:Originally posted by Roji. P. Thomas

    If you expect the procedure to work as a local proc in every database (means the proc can refer the tables in the current database), you should mark it as a system Proc.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com


    Hmm - how would mark it as a system proc?

    Panic, Chaos, Disorder ... my work here is done --unknown
  11. Roji. P. Thomas New Member

    quote:Originally posted by SQL_Guess
    Hmm - how would mark it as a system proc?

    sp_MS_marksystemobject 'Sp_name' --Undocumented, that is

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  12. SQL_Guess New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by SQL_Guess</i><br />Hmm - how would mark it as a system proc?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />sp_MS_marksystemobject 'Sp_name' --Undocumented, that is &lt;-- the evil <b><font size="3"> [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] </font id="size3"></b><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />yeah - that IS necessary. I thought sp_ would do that for me, but I got 'Invalid object name 'MyTable'' errors, until I added your line.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />USE [master]<br />IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = 'DB_A') <br />DROP DATABASE DB_A<br /><br />IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = 'DB_B') <br />DROP DATABASE DB_B<br />GO<br />CREATE DATABASE DB_A;<br />CREATE DATABASE DB_B;<br />GO<br />USE DB_A<br /><br />CREATE TABLE dbo.MyTable<br />(Cvarchar(100))<br />INSERT INTO dbo.MyTable (c) SELECT 'This is DB_A'<br /><br />USE DB_B<br /><br />CREATE TABLE dbo.MyTable<br />(Cvarchar(100))<br />INSERT INTO dbo.MyTable (c) SELECT 'This is DB_B'<br /><br />USE [master]<br />IF OBJECT_ID ('dbo.SP_ThisIsMyGlobalSP') IS NOT NULL<br />DROP PROCEDURE dbo.SP_ThisIsMyGlobalSP<br />GO<br />CREATE PROCEDURE [dbo].[SP_ThisIsMyGlobalSP]<br />@InputParametervarchar(25) <br />AS <br />BEGIN --proc<br />SET nocount on<br />SELECT'Running with DB: ['+isnull(DB_NAME(),'DBNAME')+'] received parameter ['+isnull(@InputParameter,'parameter')+'] - Data = '+C FROM MyTable<br />END --proc<br />GO<br />sp_MS_marksystemobject 'SP_ThisIsMyGlobalSP'<br />SET NOCOUNT ON<br />DECLARE @DBNamesysname <br />DECLARE@SQLCmdvarchar(250) <br /><br />DECLARE MyDBCursor CURSOR FOR SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'DB_%'<br />OPEN MyDBCursor<br />FETCH NEXT FROM MyDBCursor INTO @DBName<br />WHILE @@Fetch_status = 0 BEGIN<br />SET @SQLCmd = 'Use '+@DBName+' ;<br />EXEC SP_ThisIsMyGlobalSP ''DB execution'''<br />--SELECT @SQLCmd<br />exec (@SQLCmd)<br />FETCH NEXT FROM MyDBCursor INTO @DBName<br />END<br />CLOSE MyDBCursor<br />DEALLOCATE MyDBCursor<br /><br />USE [master]<br />IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = 'DB_A') <br />DROP DATABASE DB_A<br /><br />IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = 'DB_B') <br />DROP DATABASE DB_B<br /><br /></font id="code"></pre id="code"><br />results:<br /><pre id="code"><font face="courier" size="2" id="code"><br />----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />Running with DB: [DB_A] received parameter [DB execution] - Data = This is DB_A<br /><br /><br />----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />Running with DB: [DB_B] received parameter [DB execution] - Data = This is DB_B</font id="code"></pre id="code"><br /><br />cool - I learnt something. Thanks Roji<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  13. Roji. P. Thomas New Member

    quote:Originally posted by SQL_Guess
    cool - I learnt something. Thanks Roji
    You owe me a BEER [8D]

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  14. SQL_Guess New Member

    :beer:

    will - that used to be the icon code [8D]

    Panic, Chaos, Disorder ... my work here is done --unknown
  15. FrankKalis Moderator

    Just for completeness: sp_MS_marksystemobject is still available in SQL Server 2005, but has no functionality anymore. So, it won't break any "production code" (where it shouldn't belong anyway), but it won't do it's job either. So, if you're planning to use this on 2005, you need to find another method.

    Anyway, here's another approach that is not using a cursor


    CREATE TABLE dbs (dbname SYSNAME)
    INSERT INTO dbs
    SELECT CATALOG_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE CATALOG_NAME IN('Northwind', 'Pubs')

    DECLARE @rowcnt INT
    DECLARE @dbname SYSNAME
    DECLARE @sql NVARCHAR(1000)
    DECLARE @stmt NVARCHAR(1000)

    SELECT @sql = 'CREATE PROCEDURE dbo.Do_something AS SELECT 1'

    SELECT TOP 1 @dbname = dbname
    FROM dbs
    SET @rowcnt = @@ROWCOUNT
    WHILE @rowcnt > 0
    BEGIN
    SELECT @stmt = @dbname + '..sp_Executesql'
    EXEC @stmt @sql
    PRINT 'Procedure created in: ' + @dbname
    DELETE FROM dbs WHERE dbname = @dbname
    SELECT TOP 1 @dbname = dbname
    FROM dbs
    SET @rowcnt = @@ROWCOUNT
    END

    DROP TABLE dbs

    Typically you would modify


    WHERE CATALOG_NAME IN('Northwind', 'Pubs')

    to a NOT IN to exclude the system databases.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  16. SQL_Guess New Member

    quote:Originally posted by FrankKalis

    Just for completeness: sp_MS_marksystemobject is still available in SQL Server 2005, but has no functionality anymore.


    Have you tested that, Frank? The code I ran was done on SQL2005 (SP1 iirc), and the adding of sp_MS_marksystemobject did make a difference. I ran the code without the sp, and got the error I mentioned. Then I included the sp, and the code worked correcttly.


    quote:
    So, it won't break any "production code" (where it shouldn't belong anyway), but it won't do it's job either. So, if you're planning to use this on 2005, you need to find another method.

    Anyway, here's another approach that is not using a cursor

    Originally posted by FrankKalis
    CREATE TABLE dbs (dbname SYSNAME)
    INSERT INTO dbs
    SELECT CATALOG_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE CATALOG_NAME IN('Northwind', 'Pubs')

    DECLARE @rowcnt INT
    DECLARE @dbname SYSNAME
    DECLARE @sql NVARCHAR(1000)
    DECLARE @stmt NVARCHAR(1000)

    SELECT @sql = 'CREATE PROCEDURE dbo.Do_something AS SELECT 1'

    SELECT TOP 1 @dbname = dbname
    FROM dbs
    SET @rowcnt = @@ROWCOUNT
    WHILE @rowcnt > 0
    BEGIN
    SELECT @stmt = @dbname + '..sp_Executesql'
    EXEC @stmt @sql
    PRINT 'Procedure created in: ' + @dbname
    DELETE FROM dbs WHERE dbname = @dbname
    SELECT TOP 1 @dbname = dbname
    FROM dbs
    SET @rowcnt = @@ROWCOUNT
    END

    DROP TABLE dbs

    Typically you would modify


    WHERE CATALOG_NAME IN('Northwind', 'Pubs')

    to a NOT IN to exclude the system databases.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de

    Question - how much more efficient is a looping structure, with N stored procedure creates, over a cursor? Is it better, or were you posting a non-cursor version as fyi?

    BTW - unless I miss my guess, the poster's intent was to run the SP against the databases, rather than simply deploy it - the deployment was a seperate issue that also needed resolution. I thnk he'd like to deploy only in new db's - your script, if I understand it correctly, is a 'deploy to DBs' script? Naturally, your code could be changed to 'execution' - I'll see if I can test the performance of the cursor versus your method.

    Thanks - I'll post the code I test, and the results, and look forward to learning more.

    Panic, Chaos, Disorder ... my work here is done --unknown
  17. SQL_Guess New Member

    quote:Originally posted by FrankKalis

    ....


    --....
    SELECT CATALOG_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE CATALOG_NAME IN('Northwind', 'Pubs')


    Typically you would modify


    WHERE CATALOG_NAME IN('Northwind', 'Pubs')

    to a NOT IN to exclude the system databases.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de

    I assume that was tested in SQL2000? In SQL2005, Information_schema.Schemata doesn't appear to have DB names in it:


    quote:Originally from BOL
    Returns one row for each schema in the current database. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA.view_name.

    Important:
    This feature has changed from earlier versions of SQL Server. For more information, see Breaking Changes to Database Engine Features in SQL Server 2005.

    I think you'd need to use sys.databases in SQL2005 - I couldn't find an information_schema view with the info:



    SELECT [Name] FROM sys.databases WHERE [Name] NOT IN('Master','Model','msdb','tempdb')


    Panic, Chaos, Disorder ... my work here is done --unknown
  18. SQL_Guess New Member

    This modified version of Franks SQL works quite nicely on SQL2005 SP1 (IT DOES NOT WORK ON SQL2000 - sys.databases doesn't exist, nor does the is_read_only column exist indbo.sysdatabases), and checks for existance of SP for re-deployment:


    -- franks SQL
    SET NOCOUNT ON
    DECLARE @dbs TABLE (dbname SYSNAME)
    INSERT INTO @dbs
    SELECT [Name] FROM sys.databases WHERE [Name] NOT IN('Master','Model','msdb','tempdb') AND is_read_only = 0
    SELECT * FROM @dbs
    DECLARE @rowcnt INT
    DECLARE @dbname SYSNAME
    DECLARE @sql NVARCHAR(1000)
    DECLARE @sql2 NVARCHAR(1000)
    DECLARE @stmt NVARCHAR(1000)

    SELECT @sql= 'IF OBJECT_ID(''dbo.Do_something'') IS NOT NULL DROP PROCEDURE dbo.Do_something'
    SELECT @sql2= 'CREATE PROCEDURE dbo.Do_something AS SELECT 1'

    SELECT TOP 1 @dbname = dbname FROM @dbs
    SET @rowcnt = @@ROWCOUNT
    WHILE @rowcnt > 0 BEGIN
    SELECT @stmt = @dbname + '..sp_Executesql'
    EXEC @stmt @sql
    EXEC @stmt @sql2
    PRINT 'Procedure created in: ' + @dbname
    DELETE FROM @dbs WHERE dbname = @dbname
    SELECT TOP 1 @dbname = dbname FROM @dbs
    SET @rowcnt = @@ROWCOUNT
    END


    Sample results:


    dbname
    --------------------------------------------------------------------------------------------------------------------------------
    ReportServer$SQL2005_SP1_Test
    ReportServer$SQL2005_SP1_TestTempDB
    IntegrationServices

    Procedure created in: ReportServer$SQL2005_SP1_Test
    Procedure created in: ReportServer$SQL2005_SP1_TestTempDB
    Procedure created in: IntegrationServices


    Panic, Chaos, Disorder ... my work here is done --unknown
  19. FrankKalis Moderator

    Umh, I haven't tested that in SQL Server 2005, but I believe I recall a thread in the private MVP newgroup where it was mentioned, that sp_MS_mark... has no functionality anymore. I can't that thread now and when you say it is still working, I trust you. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Maybe one of the other MVP's here, remembers that thread?<br /><br />Sorry, about that INFORMATION_SCHEMA stuff. That has actually changed from 2000 to 2005. Now it's returning all schemas in the current database rather than all databases in the server. Looks like MS got the 2005 version into compliance with ANSI SQL. Unfortunately this is a bit confusing when one is used to work with the 2000 definition of that VIEW.<br /><br />And no, I haven't tested the performance of a cursor against a looping construct. It was just kind of "Hey, here's another approach". [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  20. SQL_Guess New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Umh, I haven't tested that in SQL Server 2005, but I believe I recall a thread in the private MVP newgroup where it was mentioned, that sp_MS_mark... has no functionality anymore. I can't that thread now and when you say it is still working, I trust you. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Maybe one of the other MVP's here, remembers that thread?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Did test it and it does make a difference. Maybe MS realized they still needed it, or haven't gotten around to the change yet (anyone say SP2 <img src='/community/emoticons/emotion-1.gif' alt=':)' /> )<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Sorry, about that INFORMATION_SCHEMA stuff. That has actually changed from 2000 to 2005. Now it's returning all schemas in the current database rather than all databases in the server. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />It is a bit difficult trying to find all the old favorites in the new views [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]. And there are still things (like databases, logins, users) for which there are not information_schema views, where we have to hit the sys.XXX views. I'm assumming that MS would rather we didn 't hit those views, or are the sys.XXX views also 'open, suppoerted' views that won't change?<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Looks like MS got the 2005 version into compliance with ANSI SQL. Unfortunately this is a bit confusing when one is used to work with the 2000 definition of that VIEW.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Nice to know the reason<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />And no, I haven't tested the performance of a cursor against a looping construct. It was just kind of "Hey, here's another approach". [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I thought it might be. It's always good to have options to see what works beswt [:0]<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  21. FrankKalis Moderator

    Hey, I've just realised you moved to the UK. In contact with Crispin?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  22. SQL_Guess New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Hey, I've just realised you moved to the UK. In contact with Crispin?<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />A few emails after I arrived, but not much recently, no. Thanks for reminding me - I should go and get hold off him and drag him out for a beer to thank him for his help getting over here.<br /><br />"touch wood" - the move was easier than expected. I got a job offer after 4 days [<img src='/community/emoticons/emotion-2.gif' alt=':D' />].<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  23. Roji. P. Thomas New Member

  24. FrankKalis Moderator

    Thanks for researching. Maybe I dreamt that I saw such a statement by Gert Drapers?!? [XX(]

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  25. Roji. P. Thomas New Member

  26. SQL_Guess New Member

    quote:Originally posted by Roji. P. Thomas


    quote:Originally posted by FrankKalis

    Thanks for researching. Maybe I dreamt that I saw such a statement by Gert Drapers?!?
    I had find a thread in a public newsgroup where he says so.
    http://groups.google.com/group/micr...5b26a?lnk=st&q=&rnum=1&hl=en#2b49aa047a65b26a

    My best guess is that it is initially disabled and reenabled later. (in the RTM/SP1 ? I am not sure. I will check it later today)

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com


    For the purposes of this, I should point out I tested and used it with SP1:
    Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

    Panic, Chaos, Disorder ... my work here is done --unknown
  27. SQL_Guess New Member

    <b><font size="3">WARNING - LOTS of code ahead</font id="size3"></b><br /><br />Frank (and anyone else interested) - it appears the non cursor mode is better (marginally). Note, I used 'my version' of Frank's code, which was using the table variables. I look forward to anyone suggesting other alternatives, which I can plug in and test.<br /><br />About the code used to load test. <br />I created some arbitrary databases (DB_A and DB_B), deploy the tables to those DB's, deploy the SP to those db's (using Franks code, btw [<img src='/community/emoticons/emotion-1.gif' alt=':)' />], deploy the SP to master. <br />Then, using a standard template I've created for load testing, I plug the relevant pieces of code into the 'measuring area' - I've moved delcare etc. outside where possible, so we can measure execution time of the code.<br /><br />Testing done on SQL 2005 SP1, with only 2 DB's - I could try with more DB's, I guess.<br /><br />I look forward to comments, improvements, suggestions, free beer etc. etc.<br /><br />the results on 1000 iterations:<br /><pre id="code"><font face="courier" size="2" id="code"><br />CodeTested OverallDurationMS AverageDurationMS<br />---------------------------------------------------------------------------------------------------- ----------------- ---------------------------------------<br />Frank non-cursor Code 2110 2.11000<br />Cursor Code 2420 2.42000<br /></font id="code"></pre id="code"><br /><br />The test harnass, with all the object creates etc.<br /><pre id="code"><font face="courier" size="2" id="code"><br />--################################################## Create DBs #########################################################################<br /><br />USE [master]<br />IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = 'DB_A') <br />DROP DATABASE DB_A<br /><br />IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = 'DB_B') <br />DROP DATABASE DB_B<br />GO<br />CREATE DATABASE DB_A;<br />CREATE DATABASE DB_B;<br />GO<br />--################################################## Create TBs #########################################################################<br /><br />USE DB_A<br /><br />CREATE TABLE dbo.MyTable<br />(Cvarchar(100))<br />INSERT INTO dbo.MyTable (c) SELECT 'This is DB_A'<br /><br />USE DB_B<br /><br />CREATE TABLE dbo.MyTable<br />(Cvarchar(100))<br />INSERT INTO dbo.MyTable (c) SELECT 'This is DB_B'<br /><br />--################################################## Create SP #########################################################################<br />--for use by global invocation<br />USE [master]<br />IF OBJECT_ID ('dbo.SP_ThisIsMyGlobalSP') IS NOT NULL<br />DROP PROCEDURE dbo.SP_ThisIsMyGlobalSP<br />GO<br />CREATE PROCEDURE [dbo].[SP_ThisIsMyGlobalSP]<br />@InputParametervarchar(25) <br />AS <br />BEGIN --proc<br />SET nocount on<br />SELECT'Running with DB: ['+isnull(DB_NAME(),'DBNAME')+'] received parameter ['+isnull(@InputParameter,'parameter')+'] - Data = '+C FROM MyTable<br />END --proc<br />GO<br />--for use by local invocation<br />SET NOCOUNT ON<br />DECLARE @dbsa TABLE (dbname SYSNAME)<br />INSERT INTO @dbsa<br />SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'DB_%'<br /><br />DECLARE @rowcnta INT<br />DECLARE @dbnamea SYSNAME<br />DECLARE @sql3a NVARCHAR(1000)<br />DECLARE @stmta NVARCHAR(1000)<br /><br />SELECT @sql3a= 'CREATE PROCEDURE [dbo].[SP_ThisIsMyGlobalSP]<br />@InputParametervarchar(25) <br />AS <br />BEGIN --proc<br />SET nocount on<br />SELECT''Running with DB: [''+isnull(DB_NAME(),''DBNAME'')+''] received parameter [''+isnull(@InputParameter,''parameter'')+''] - Data = ''+C FROM MyTable<br />END'<br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />WHILE @rowcnta &gt; 0 BEGIN<br /> SELECT @stmta = @dbnamea + '..sp_Executesql'<br /> EXEC @stmta @sql3a<br /> DELETE FROM @dbsa WHERE dbname = @dbnamea<br /> SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br /> SET @rowcnta = @@ROWCOUNT<br /> END<br />--################################################## COMMENTS ########################################################################################<br /><br />--Created By: Regan Galbraith<br />--Created On: 2006-06-16<br /><br />--Version: 0.1 - initial creation<br /><br />--SQL Version: 2005 SP1. <br /><br />--Overview:<br />--This script is a generic load test harnass. It uses looping to test some SQL code iteratively, with DBCC DROPCLEANBUFFERS and FREEPROCCACHE to <br />--ensure fair comparison.<br /><br />--ToDo:<br />-- &lt;ToDo&gt;<br /><br />--###########SELECT####################################### DECLARE & SET VARIABLES #########################################################################<br />--###################################################### describe script #############################################################################<br />DECLARE @RunDateDATETIME<br />SET@RunDate= CURRENT_TIMESTAMP<br />PRINT'&gt;&gt;&gt; This script LOAD TESTS the following Stored Procedures: &lt;&lt;&lt;'<br />PRINT' &gt;&gt;&gt; your Code versions &lt;&lt;&lt;'<br />PRINT'&gt;&gt;&gt; This execution on server: ['+@@SERVERNAME+'] started at: ['+CONVERT(VARCHAR,@RunDate,113)+'] &lt;&lt;&lt;'<br />PRINT''<br />--<br />SET NOCOUNT ON<br />DECLARE <br />@LoopsToDoINT,<br />@LoopCounterINT,<br />@StartDatetimeDATETIME,<br />@EndDatetimeDATETIME,<br />@CodeTestedVARCHAR(100),<br />@ReturnCodeINT,<br />@DebugLevelTINYINT<br /><br />DECLARE<br />@DurationResultTABLE<br />(UIDINTIDENTITY(1,1),<br />CodeTestedVARCHAR(100),<br />OverallDurationMSINT,<br />AverageDurationMSDECIMAL(15,5))<br /><br />IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE [Name] = '##TestData')<br />DROP TABLE ##TestData<br />CREATE TABLE ##TestData<br />(RuleTextNVARCHAR(MAX))<br /><br />DECLARE<br />@RuleSetTextNVARCHAR(MAX),<br />@SQLImportCMDVARCHAR(204<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />@RuleFilePathVARCHAR(255),<br />@RuleSetTokenNCHAR(13),<br />@CalculateTokenDATETIME<br /><br /><br />--################################################## Set Global Variables ############################################################################<br />SET @LoopsToDo= 1000<br />SET @LoopCounter= 0<br />SET @DebugLevel= 0-- 0 = display no variables, 1 = display table changes, 2 = display table changes + variables<br />SET @CalculateToken = DATEADD(DAY,2,CURRENT_TIMESTAMP)<br /><br />IF (@DebugLevel &gt; 1) SELECT @CalculateToken<br />--&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; BEGIN TEST CODE BLOCK&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br />--Declare, Set and infrom begin of CODE BLOCK<br />-- Name the section of Code you are testing. This is Specifically useful when doing comparitive testing of several version of code<br />SET @CodeTested = 'Frank non-cursor Code'<br />PRINT'--- TESTING CODE BLOCK : ['+@CodeTested+']'<br /><br />--Set Local variables<br />-- This will be necessary for a specific block, and may be necessary for several blocks of code.<br />-- The principle when testing several pieces of code is that we want to use the same variables, and that creation of variables, values etc. should,<br />-- as far as possible, be done outside the measuring zone.<br /><br />IF (@DebugLevel &gt; 1) <br /><br />--################################################## Execute Code Start ##############################################################################<br />--################################################## Clear Caches ####################################################################################<br />DBCC FREEPROCCACHE WITH NO_INFOMSGS<br />DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS<br />--prepare for loop<br />SET @LoopCounter= 0<br />DECLARE @dbs TABLE (dbname SYSNAME)<br />DECLARE @rowcnt INT<br />DECLARE @dbname SYSNAME<br />DECLARE @sql3 NVARCHAR(1000)<br />DECLARE @stmt NVARCHAR(1000)<br /><br />------------------------------------------------------------Measuring Zone START<br />SET @StartDatetime= CURRENT_TIMESTAMP--Work Starting<br />WHILE @LoopCounter &lt; @LoopsToDo BEGIN<br />SET @LoopCounter = @LoopCounter + 1<br />SET NOCOUNT ON<br />INSERT INTO @dbs<br />SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'DB_%'<br /><br />SELECT @sql3= 'EXEC SP_ThisIsMyGlobalSP ''DB execution'''<br />SELECT TOP 1 @dbname = dbname FROM @dbs<br />SET @rowcnt = @@ROWCOUNT<br />WHILE @rowcnt &gt; 0 BEGIN<br />SELECT @stmt = @dbname + '..sp_Executesql'<br />EXEC @stmt @sql3<br />DELETE FROM @dbs WHERE dbname = @dbname<br />SELECT TOP 1 @dbname = dbname FROM @dbs<br />SET @rowcnt = @@ROWCOUNT<br />END<br />END<br />SET @EndDatetime= CURRENT_TIMESTAMP--Work Done<br /><br />------------------------------------------------------------Measuring Zone END<br />-- log Duration<br />INSERT INTO @DurationResult (CodeTested, OverallDurationMS, AverageDurationMS )<br />SELECT@CodeTested, <br />DATEDIFF(ms,@StartDatetime,@EndDatetime), <br />CONVERT(DECIMAL(15,5),((CONVERT(DECIMAL(15,5),DATEDIFF(ms,@StartDatetime,@EndDatetime)))/@LoopCounter))<br />--################################################## Execute Code Finish #############################################################################<br />--report of data changed<br /><br />--&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; END TEST CODE BLOCK &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br />--&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; BEGIN TEST CODE BLOCK&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br />--Declare, Set and infrom begin of CODE BLOCK<br />-- Name the section of Code you are testing. This is Specifically useful when doing comparitive testing of several version of code<br />SET @CodeTested = 'Cursor Code'<br />PRINT'--- TESTING CODE BLOCK : ['+@CodeTested+']'<br /><br />--Set Local variables<br />-- This will be necessary for a specific block, and may be necessary for several blocks of code.<br />-- The principle when testing several pieces of code is that we want to use the same variables, and that creation of variables, values etc. should,<br />-- as far as possible, be done outside the measuring zone.<br /><br />--################################################## Execute Code Start ##############################################################################<br />--################################################## Clear Caches ####################################################################################<br />DBCC FREEPROCCACHE WITH NO_INFOMSGS<br />DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS<br />--prepare for loop<br />SET @LoopCounter= 0<br />EXEC [master]..sp_MS_marksystemobject 'SP_ThisIsMyGlobalSP'<br />DECLARE @DBName2sysname <br />DECLARE@SQLCmdvarchar(250) <br /><br />------------------------------------------------------------Measuring Zone START<br />SET @StartDatetime= CURRENT_TIMESTAMP--Work Starting<br /><br />WHILE @LoopCounter &lt; @LoopsToDo BEGIN<br />SET @LoopCounter = @LoopCounter + 1<br />DECLARE MyDBCursor CURSOR FOR SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'DB_%'<br />OPEN MyDBCursor<br />FETCH NEXT FROM MyDBCursor INTO @DBName2<br />WHILE @@Fetch_status = 0 BEGIN<br />SET @SQLCmd = 'Use '+@DBName2+' ;EXEC SP_ThisIsMyGlobalSP ''DB execution'''<br />EXEC (@SQLCmd)<br />FETCH NEXT FROM MyDBCursor INTO @DBName2<br />END<br />CLOSE MyDBCursor<br />DEALLOCATE MyDBCursor<br />END<br />SET @EndDatetime= CURRENT_TIMESTAMP--Work Done<br /><br />------------------------------------------------------------Measuring Zone END<br />-- log Duration<br />INSERT INTO @DurationResult (CodeTested, OverallDurationMS, AverageDurationMS )<br />SELECT@CodeTested, <br />DATEDIFF(ms,@StartDatetime,@EndDatetime), <br />CONVERT(DECIMAL(15,5),((CONVERT(DECIMAL(15,5),DATEDIFF(ms,@StartDatetime,@EndDatetime)))/@LoopCounter))<br />--################################################## Execute Code Finish #############################################################################<br />--report of data changed<br /><br />--&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; END TEST CODE BLOCK &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br /><br />--###################################################### end script ##################################################################################<br />--Report on script results and duration<br />SELECT CodeTested, OverallDurationMS, AverageDurationMS FROM @DurationResult ORDER BY AverageDurationMS ASC<br />PRINT''<br />PRINT'&gt;&gt;&gt; This execution on server: ['+@@SERVERNAME+'] completed at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] &lt;&lt;&lt;'<br />PRINT'&gt;&gt;&gt; The execution duration was: ['+CONVERT(VARCHAR(10),DATEDIFF(ms,CONVERT(VARCHAR,@RunDate,113),CURRENT_TIMESTAMP))+'] Milliseconds &lt;&lt;&lt;'<br /><br />USE [master]<br />IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = 'DB_A') <br />DROP DATABASE DB_A<br /><br />IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = 'DB_B') <br />DROP DATABASE DB_B<br /></font id="code"></pre id="code"><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  28. SQL_Guess New Member

    Tested across 10 databases for 1000 iterations - results still lean towards Frank's code:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />CodeTested OverallDurationMS AverageDurationMS<br />---------------------------------------------------------------------------------------------------- ----------------- ---------------------------------------<br />Frank non-cursor Code 9420 9.42000<br />Cursor Code 10216 10.21600<br /></font id="code"></pre id="code"><br /><br />15 DBs:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />CodeTested OverallDurationMS AverageDurationMS<br />---------------------------------------------------------------------------------------------------- ----------------- ---------------------------------------<br />Frank non-cursor Code 13533 13.53300<br />Cursor Code 15780 15.78000<br /></font id="code"></pre id="code"><br />Also, it was quite cool using Franks code to do all my DB,TB and SP creation, and Drop DB:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />--################################################## Create DBs #########################################################################<br /><br />USE [master]<br /><br />SET NOCOUNT ON<br />DECLARE @dbsa TABLE (dbname SYSNAME)<br />INSERT INTO@dbsa (dbname)<br />SELECT 'DB_A'<br />UNIONSELECT 'DB_B'<br />UNIONSELECT 'DB_C'<br />UNIONSELECT 'DB_D'<br />UNIONSELECT 'DB_E'<br />UNIONSELECT 'DB_F'<br />UNIONSELECT 'DB_G'<br />UNIONSELECT 'DB_H'<br />UNIONSELECT 'DB_I'<br />UNIONSELECT 'DB_J'<br />UNIONSELECT 'DB_K'<br />UNIONSELECT 'DB_L'<br />UNIONSELECT 'DB_M'<br />UNIONSELECT 'DB_N'<br />UNIONSELECT 'DB_O'<br /><br />DECLARE @rowcnta INT<br />DECLARE @dbnamea SYSNAME<br />DECLARE @sql3a NVARCHAR(1000)<br />DECLARE @stmta NVARCHAR(1000)<br /><br />PRINT '--------------------------------------- DATABASES ---------------------------------------'<br /><br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />WHILE @rowcnta &gt; 0 BEGIN<br />PRINT'Drop Database '+@dbnamea<br />SELECT @sql3a= 'IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = '''+@dbnamea+''') DROP DATABASE '+@dbnamea+''<br />SELECT @stmta = 'master..sp_Executesql'<br />EXEC @stmta @sql3a<br />PRINT'Create Database '+@dbnamea<br />SELECT @sql3a= 'CREATE DATABASE '+@dbnamea+' '<br />SELECT @stmta = 'master..sp_Executesql'<br />EXEC @stmta @sql3a<br />DELETE FROM @dbsa WHERE dbname = @dbnamea<br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />PRINT ''<br />END<br />GO<br />--################################################## Create TBs #########################################################################<br /><br />DECLARE @rowcnta INT<br />DECLARE @dbnamea SYSNAME<br />DECLARE @sql3a NVARCHAR(1000)<br />DECLARE @stmta NVARCHAR(1000)<br /><br />DECLARE @dbsa TABLE (dbname SYSNAME)<br />INSERT INTO@dbsa (dbname)<br />SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'DB_%'<br />PRINT '--------------------------------------- TABLES ---------------------------------------'<br /><br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />WHILE @rowcnta &gt; 0 BEGIN<br />PRINT'Create Table in Database '+@dbnamea<br />SELECT @sql3a= 'Use ['+@dbnamea+']; CREATE TABLE dbo.MyTable (Cvarchar(100)); INSERT INTO dbo.MyTable (c) SELECT ''This is '+@dbnamea+''''<br />--SELECT @sql3a<br />SELECT @stmta = 'master..sp_Executesql'<br />--SELECT @stmta<br />EXEC @stmta @sql3a<br />DELETE FROM @dbsa WHERE dbname = @dbnamea<br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />END<br /><br />/*<br />USE DB_A<br /><br />CREATE TABLE dbo.MyTable (Cvarchar(100))<br />INSERT INTO dbo.MyTable (c) SELECT 'This is DB_A'<br /><br />USE DB_B<br /><br />CREATE TABLE dbo.MyTable<br />(Cvarchar(100))<br />INSERT INTO dbo.MyTable (c) SELECT 'This is DB_B'<br />*/<br />--################################################## Create SP #########################################################################<br />--for use by global invocation<br />USE [master]<br />IF OBJECT_ID ('dbo.SP_ThisIsMyGlobalSP') IS NOT NULL<br />DROP PROCEDURE dbo.SP_ThisIsMyGlobalSP<br />GO<br />CREATE PROCEDURE [dbo].[SP_ThisIsMyGlobalSP]<br />@InputParametervarchar(25) <br />AS <br />BEGIN --proc<br />SET nocount on<br />SELECT'Running with DB: ['+isnull(DB_NAME(),'DBNAME')+'] received parameter ['+isnull(@InputParameter,'parameter')+'] - Data = '+C FROM MyTable<br />END --proc<br />GO<br />--for use by local invocation<br />SET NOCOUNT ON<br /><br />PRINT '--------------------------------------- STORED PROCEDURES ---------------------------------------'<br /><br />DECLARE @dbsa TABLE (dbname SYSNAME)<br />INSERT INTO @dbsa<br />SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'DB_%'<br /><br />DECLARE @rowcnta INT<br />DECLARE @dbnamea SYSNAME<br />DECLARE @sql3a NVARCHAR(1000)<br />DECLARE @stmta NVARCHAR(1000)<br /><br />SELECT @sql3a= 'CREATE PROCEDURE [dbo].[SP_ThisIsMyGlobalSP]<br />@InputParametervarchar(25) <br />AS <br />BEGIN --proc<br />SET nocount on<br />SELECT''Running with DB: [''+isnull(DB_NAME(),''DBNAME'')+''] received parameter [''+isnull(@InputParameter,''parameter'')+''] - Data = ''+C FROM MyTable<br />END'<br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />WHILE @rowcnta &gt; 0 BEGIN<br />PRINT'Create SP in Database '+@dbnamea<br />SELECT @stmta = @dbnamea + '..sp_Executesql'<br />EXEC @stmta @sql3a<br />DELETE FROM @dbsa WHERE dbname = @dbnamea<br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />END<br />--################################################## COMMENTS ########################################################################################<br /><br />--Created By: Regan Galbraith<br />--Created On: 2006-06-16<br /><br />--Version: 0.1 - initial creation<br /><br />--SQL Version: 2005 SP1. <br /><br />--Overview:<br />--This script is a generic load test harnass. It uses looping to test some SQL code iteratively, with DBCC DROPCLEANBUFFERS and FREEPROCCACHE to <br />--ensure fair comparison.<br /><br />--ToDo:<br />-- &lt;ToDo&gt;<br /><br />--###########SELECT####################################### DECLARE & SET VARIABLES #########################################################################<br />--###################################################### describe script #############################################################################<br />DECLARE @RunDateDATETIME<br />SET@RunDate= CURRENT_TIMESTAMP<br />PRINT'&gt;&gt;&gt; This script LOAD TESTS the following Stored Procedures: &lt;&lt;&lt;'<br />PRINT' &gt;&gt;&gt; your Code versions &lt;&lt;&lt;'<br />PRINT'&gt;&gt;&gt; This execution on server: ['+@@SERVERNAME+'] started at: ['+CONVERT(VARCHAR,@RunDate,113)+'] &lt;&lt;&lt;'<br />PRINT''<br />--<br />SET NOCOUNT ON<br />DECLARE <br />@LoopsToDoINT,<br />@LoopCounterINT,<br />@StartDatetimeDATETIME,<br />@EndDatetimeDATETIME,<br />@CodeTestedVARCHAR(100),<br />@ReturnCodeINT,<br />@DebugLevelTINYINT<br /><br />DECLARE<br />@DurationResultTABLE<br />(UIDINTIDENTITY(1,1),<br />CodeTestedVARCHAR(100),<br />OverallDurationMSINT,<br />AverageDurationMSDECIMAL(15,5))<br /><br />IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE [Name] = '##TestData')<br />DROP TABLE ##TestData<br />CREATE TABLE ##TestData<br />(RuleTextNVARCHAR(MAX))<br /><br />DECLARE<br />@RuleSetTextNVARCHAR(MAX),<br />@SQLImportCMDVARCHAR(204<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />@RuleFilePathVARCHAR(255),<br />@RuleSetTokenNCHAR(13),<br />@CalculateTokenDATETIME<br /><br /><br />--################################################## Set Global Variables ############################################################################<br />SET @LoopsToDo= 1000<br />SET @LoopCounter= 0<br />SET @DebugLevel= 0-- 0 = display no variables, 1 = display table changes, 2 = display table changes + variables<br />SET @CalculateToken = DATEADD(DAY,2,CURRENT_TIMESTAMP)<br /><br />IF (@DebugLevel &gt; 1) SELECT @CalculateToken<br />--&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; BEGIN TEST CODE BLOCK&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br />--Declare, Set and infrom begin of CODE BLOCK<br />-- Name the section of Code you are testing. This is Specifically useful when doing comparitive testing of several version of code<br />SET @CodeTested = 'Frank non-cursor Code'<br />PRINT'--- TESTING CODE BLOCK : ['+@CodeTested+']'<br /><br />--Set Local variables<br />-- This will be necessary for a specific block, and may be necessary for several blocks of code.<br />-- The principle when testing several pieces of code is that we want to use the same variables, and that creation of variables, values etc. should,<br />-- as far as possible, be done outside the measuring zone.<br /><br />IF (@DebugLevel &gt; 1) <br /><br />--################################################## Execute Code Start ##############################################################################<br />--################################################## Clear Caches ####################################################################################<br />DBCC FREEPROCCACHE WITH NO_INFOMSGS<br />DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS<br />--prepare for loop<br />SET @LoopCounter= 0<br />DECLARE @dbs TABLE (dbname SYSNAME)<br />DECLARE @rowcnt INT<br />DECLARE @dbname SYSNAME<br />DECLARE @sql3 NVARCHAR(1000)<br />DECLARE @stmt NVARCHAR(1000)<br /><br />------------------------------------------------------------Measuring Zone START<br />SET @StartDatetime= CURRENT_TIMESTAMP--Work Starting<br />WHILE @LoopCounter &lt; @LoopsToDo BEGIN<br />SET @LoopCounter = @LoopCounter + 1<br />SET NOCOUNT ON<br />INSERT INTO @dbs<br />SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'DB_%'<br /><br />SELECT @sql3= 'EXEC SP_ThisIsMyGlobalSP ''DB execution'''<br />SELECT TOP 1 @dbname = dbname FROM @dbs<br />SET @rowcnt = @@ROWCOUNT<br />WHILE @rowcnt &gt; 0 BEGIN<br />SELECT @stmt = @dbname + '..sp_Executesql'<br />EXEC @stmt @sql3<br />DELETE FROM @dbs WHERE dbname = @dbname<br />SELECT TOP 1 @dbname = dbname FROM @dbs<br />SET @rowcnt = @@ROWCOUNT<br />END<br />END<br />SET @EndDatetime= CURRENT_TIMESTAMP--Work Done<br /><br />------------------------------------------------------------Measuring Zone END<br />-- log Duration<br />INSERT INTO @DurationResult (CodeTested, OverallDurationMS, AverageDurationMS )<br />SELECT@CodeTested, <br />DATEDIFF(ms,@StartDatetime,@EndDatetime), <br />CONVERT(DECIMAL(15,5),((CONVERT(DECIMAL(15,5),DATEDIFF(ms,@StartDatetime,@EndDatetime)))/@LoopCounter))<br />--################################################## Execute Code Finish #############################################################################<br />--report of data changed<br /><br />--&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; END TEST CODE BLOCK &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br />--&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; BEGIN TEST CODE BLOCK&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br />--Declare, Set and infrom begin of CODE BLOCK<br />-- Name the section of Code you are testing. This is Specifically useful when doing comparitive testing of several version of code<br />SET @CodeTested = 'Cursor Code'<br />PRINT'--- TESTING CODE BLOCK : ['+@CodeTested+']'<br /><br />--Set Local variables<br />-- This will be necessary for a specific block, and may be necessary for several blocks of code.<br />-- The principle when testing several pieces of code is that we want to use the same variables, and that creation of variables, values etc. should,<br />-- as far as possible, be done outside the measuring zone.<br /><br />--################################################## Execute Code Start ##############################################################################<br />--################################################## Clear Caches ####################################################################################<br />DBCC FREEPROCCACHE WITH NO_INFOMSGS<br />DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS<br />--prepare for loop<br />SET @LoopCounter= 0<br />EXEC [master]..sp_MS_marksystemobject 'SP_ThisIsMyGlobalSP'<br />DECLARE @DBName2sysname <br />DECLARE@SQLCmdvarchar(250) <br /><br />------------------------------------------------------------Measuring Zone START<br />SET @StartDatetime= CURRENT_TIMESTAMP--Work Starting<br /><br />WHILE @LoopCounter &lt; @LoopsToDo BEGIN<br />SET @LoopCounter = @LoopCounter + 1<br />DECLARE MyDBCursor CURSOR FOR SELECT [Name] FROM sys.databases WHERE [Name] LIKE 'DB_%'<br />OPEN MyDBCursor<br />FETCH NEXT FROM MyDBCursor INTO @DBName2<br />WHILE @@Fetch_status = 0 BEGIN<br />SET @SQLCmd = 'Use '+@DBName2+' ;EXEC SP_ThisIsMyGlobalSP ''DB execution'''<br />EXEC (@SQLCmd)<br />FETCH NEXT FROM MyDBCursor INTO @DBName2<br />END<br />CLOSE MyDBCursor<br />DEALLOCATE MyDBCursor<br />END<br />SET @EndDatetime= CURRENT_TIMESTAMP--Work Done<br /><br />------------------------------------------------------------Measuring Zone END<br />-- log Duration<br />INSERT INTO @DurationResult (CodeTested, OverallDurationMS, AverageDurationMS )<br />SELECT@CodeTested, <br />DATEDIFF(ms,@StartDatetime,@EndDatetime), <br />CONVERT(DECIMAL(15,5),((CONVERT(DECIMAL(15,5),DATEDIFF(ms,@StartDatetime,@EndDatetime)))/@LoopCounter))<br />--################################################## Execute Code Finish #############################################################################<br />--report of data changed<br /><br />--&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt; END TEST CODE BLOCK &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br /><br />--###################################################### end script ##################################################################################<br />--Report on script results and duration<br />SELECT CodeTested, OverallDurationMS, AverageDurationMS FROM @DurationResult ORDER BY AverageDurationMS ASC<br />PRINT''<br />PRINT'&gt;&gt;&gt; This execution on server: ['+@@SERVERNAME+'] completed at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] &lt;&lt;&lt;'<br />PRINT'&gt;&gt;&gt; The execution duration was: ['+CONVERT(VARCHAR(10),DATEDIFF(ms,CONVERT(VARCHAR,@RunDate,113),CURRENT_TIMESTAMP))+'] Milliseconds &lt;&lt;&lt;'<br />GO<br />USE [master]<br /><br />SET NOCOUNT ON<br />DECLARE @dbsa TABLE (dbname SYSNAME)<br />INSERT INTO@dbsa (dbname)<br />SELECT 'DB_A'<br />UNIONSELECT 'DB_B'<br />UNIONSELECT 'DB_C'<br />UNIONSELECT 'DB_D'<br />UNIONSELECT 'DB_E'<br />UNIONSELECT 'DB_F'<br />UNIONSELECT 'DB_G'<br />UNIONSELECT 'DB_H'<br />UNIONSELECT 'DB_I'<br />UNIONSELECT 'DB_J'<br />UNIONSELECT 'DB_K'<br />UNIONSELECT 'DB_L'<br />UNIONSELECT 'DB_M'<br />UNIONSELECT 'DB_N'<br />UNIONSELECT 'DB_O'<br /><br />DECLARE @rowcnta INT<br />DECLARE @dbnamea SYSNAME<br />DECLARE @sql3a NVARCHAR(1000)<br />DECLARE @stmta NVARCHAR(1000)<br /><br />PRINT '--------------------------------------- DATABASES ---------------------------------------'<br /><br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />WHILE @rowcnta &gt; 0 BEGIN<br />PRINT'Drop Database '+@dbnamea<br />SELECT @sql3a= 'IF EXISTS (SELECT [Name] FROM [master].sys.sysdatabases WHERE [Name] = '''+@dbnamea+''') DROP DATABASE '+@dbnamea+''<br />SELECT @stmta = 'master..sp_Executesql'<br />EXEC @stmta @sql3a<br />DELETE FROM @dbsa WHERE dbname = @dbnamea<br />SELECT TOP 1 @dbnamea = dbname FROM @dbsa<br />SET @rowcnta = @@ROWCOUNT<br />PRINT ''<br />END<br />GO<br /></font id="code"></pre id="code"><br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  29. SQL_Guess New Member

    @Bubberz

    Hopefully all of the code above will give you clues to help. Using Frank's non-cursor code seems more performant, doesn't use an undocumented MS SP, and can be used to re-deploy your stored procedures - use a variation of what I do checking the existance of databases, and dropping them - you coiuld do the same and drop and re-create SP, or alternatively if exists then alter, if not then create.

    HTH

    Panic, Chaos, Disorder ... my work here is done --unknown
  30. FrankKalis Moderator

    Wow, that's really interesting! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Probably we should put this into the script section here.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  31. Roji. P. Thomas New Member

    quote:Originally posted by SQL_Guess

    @Bubberz

    Hopefully all of the code above will give you clues to help. Using Frank's non-cursor code seems more performant, doesn't use an undocumented MS SP, and can be used to re-deploy your stored procedures - use a variation of what I do checking the existance of databases, and dropping them - you coiuld do the same and drop and re-create SP, or alternatively if exists then alter, if not then create.

    The subtle differnce between the sp_MS_marksystemobject approach and the above apprach is that, with the first approach, you create the procedure in the master database and it will be readily available for new and existing databases. The later approach requires you to run the code everytime you create a new database.

    Still developing a solution using a undocumented procedure is a bad idea.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  32. SQL_Guess New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Wow, that's really interesting! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Probably we should put this into the script section here.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Well, I'll be interested to see it there. BTW, do you agree/have opinions on the changes I made to your base version to move from temp tables to table variables? IIRC, the table variables should be faster since they will be in memory, whereas the temp table will result in IO operations on tempdb. Correct?<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  33. SQL_Guess New Member

    quote:Originally posted by Roji. P. Thomas


    quote:Originally posted by SQL_Guess

    @Bubberz

    Hopefully all of the code above will give you clues to help. Using Frank's non-cursor code seems more performant, doesn't use an undocumented MS SP, and can be used to re-deploy your stored procedures - use a variation of what I do checking the existance of databases, and dropping them - you coiuld do the same and drop and re-create SP, or alternatively if exists then alter, if not then create.

    The subtle differnce between the sp_MS_marksystemobject approach and the above apprach is that, with the first approach, you create the procedure in the master database and it will be readily available for new and existing databases. The later approach requires you to run the code everytime you create a new database.

    Still developing a solution using a undocumented procedure is a bad idea.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com


    Another difference/benefit of the "sp_MS_marksystemobject approach " is that the code is deployed once, to a single location, and as such maintenance is simplified, and there is no risk of out-of-synch copies of the code if/when changes are made...

    Panic, Chaos, Disorder ... my work here is done --unknown
  34. Roji. P. Thomas New Member

  35. SQL_Guess New Member

    Wow - thanks for that, Roji. A few useful things there that I wasn't aware of. I assume that all still holds true for SQL2005?

    The points I felt were new to me:

    quote:
    Contrary to the popular belief that table variables exists in memory, KB Article 305977 states that "A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)."

    Stored Procedure Recompilations. Using table variables can reduce the number of stored procedure recompilations. thats one place where table variables excel over temp tables.

    Table variables do not maintain statistics like temporary tables. This might lead to bad execution plan and slow performance when dealing with higher number of rows.


    Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page