February 21, 2017 at 8:46 am
Hi,
New accidental DBA, here. Our instance has some non-prod DBs that are different, but most are prod-DBs that have the exact same tables/structure, but each has entirely different data. I'm creating some function(s) to make it easier on the more novice users. However, we have 125+ DBs to deploy these functions across and quite frankly, I don't want to have to run 125+ separate commands. So, I'm trying to find a way to do this in one fell swoop. I've seen some ideas on the internet regarding the use of synonyms. Now, unless I'm misunderstanding what that is, exactly, it seems like that would not work in our case.
This is why: Say I create the function on a database, and it is simply just returning one value from one table. I go ahead and make a bunch of synonyms for this function. If they use the synonym in a database that is not the one it was created in, they will get the wrong info/none at all because it's calling back on the specific table and it's data from the db the function resides in. Am I wrong about that?
Please help.
Thanks
February 21, 2017 at 9:22 am
I would start by learning about this:
https://www.mssqltips.com/sqlservertip/2445/sql-server-multi-server-administration/
This is about setting SQL Server Agent jobs on multiple servers. The concept can also be applied to running scripts on multiple databases.
You can also find more in Books Online.
You could also look at RedGates Multiscript tool.
February 21, 2017 at 9:30 am
There are a number of tools available that will run scripts on multiple databases on multiple servers in one fell swoop. If you know scripting it is almost trivial, especially with PowerShell.
HOWEVER: PLEASE reconsider your use of Scalar UDFs!!!!! They are just horrific if used in queries. They prevent the acquisition of valid statistics, meaning bad plans thus bad performance AND often bad concurrency, can prevent the use of index seeks, prevent parallel queries, etc etc!!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 21, 2017 at 9:53 am
Thank you both for your input and help.
TheSQLGuru - Tuesday, February 21, 2017 9:30 AMThere are a number of tools available that will run scripts on multiple databases on multiple servers in one fell swoop. If you know scripting it is almost trivial, especially with PowerShell.HOWEVER: PLEASE reconsider your use of Scalar UDFs!!!!! They are just horrific if used in queries. They prevent the acquisition of valid statistics, meaning bad plans thus bad performance AND often bad concurrency, can prevent the use of index seeks, prevent parallel queries, etc etc!!!!
You know, I didn't actually ever read about that in my studies. At least not that I remember. Why is that? Just because it's got to complete the query for the function, return the value, and then complete the outer query? Is this true for all functions and SPs, or just scalar functions specifically?
Additionally, the function won't be used by the application; only in ad-hoc queries written by our (small) support department. There won't be many run at once, or even in quick succession. Is it still not advisable at that point?
Thanks for your time and info!
February 21, 2017 at 10:05 am
scarr030 - Tuesday, February 21, 2017 9:53 AMThank you both for your input and help.TheSQLGuru - Tuesday, February 21, 2017 9:30 AMThere are a number of tools available that will run scripts on multiple databases on multiple servers in one fell swoop. If you know scripting it is almost trivial, especially with PowerShell.HOWEVER: PLEASE reconsider your use of Scalar UDFs!!!!! They are just horrific if used in queries. They prevent the acquisition of valid statistics, meaning bad plans thus bad performance AND often bad concurrency, can prevent the use of index seeks, prevent parallel queries, etc etc!!!!
You know, I didn't actually ever read about that in my studies. At least not that I remember. Why is that? Just because it's got to complete the query for the function, return the value, and then complete the outer query? Is this true for all functions and SPs, or just scalar functions specifically?
Additionally, the function won't be used by the application; only in ad-hoc queries written by our (small) support department. There won't be many run at once, or even in quick succession. Is it still not advisable at that point?
Thanks for your time and info!
Oh, they also lead to Row By Agonizing Row processing under the covers. The reason for all of those things is that Scalar AND Multi-Statement Table Valued Functions are not integrated in to the optimization system nor the execution system of SQL Server.
There is almost always a way to avoid both of those. Either Inline TVF (which doesn't suffer the same issues) or just integrate the guts of the logic into your queries. I once had to use THREE TEMP TABLES to be able to eliminate a very complex UDF from a single SELECT statement. The entire code averaged almost FOUR ORDERS OF MAGNITUDE more efficient and NEVER went out to lunch as sometimes happens with code that monkeys with the optimizer's abilities.
Try to find a copy of the SQL Server MVP Deep Dives 2 book, and read my chapter entitled "Death by UDF". 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply