Last post Aug 31, 2010 02:52 AM by riswadkarharshad
Feb 09, 2010 02:57 PM|joeastral|LINK
In an effort to always improve my knowledge of performance tuning for SQL Server 2000/2005- I'm hoping others will be willing to share their top N lists of things you should not do in tsql sprocs and user defined functions. I'll submit some of my top no-no's
but I'm hoping others can share their knowledge as well that may be more advanced than mine.
1. Do not use build dynamic SQL statements that cannot be cached for optimal performance plans. So always use parameters in your dynamic sql in your stored procedures. If someone ran a query for the top N slowest queries on your box, none of them should
be something like OriginDate >= '1/5/2009' but instead OriginDate >= @OriginDate.
2. Do not use correlated subqueries on anything that needs to go fast. I only count the rare lazy scheduled report as a candidate for a correllated subquery. Otherwise the question to ask is how can I either put this in a join or restructure my database
so that this query can run more efficiently. (ex. select h.empid, (select i.name from emp i where i.empid = h.empid) as 'name' from hrdata h )
3. Do not be comforted if things run fast on the test database. If you can, always perform a SQL Profiler trace on your demo application before leaving in the lonely cycle of maintenance. I like using the Profiler because it gives you test parameters
to use for all of your stored procedures so you can then take that and get actual execution plans in sql server management studio. Then you can scan for any conspicuous table scans or clustered index scans that don't belong. You'll sleep better at night
knowing your application isn't going to crash in some fatal unoptimized slow down one day.
4. Don't put every function and your mom's functions all in one big query. You might find your tempdb or transaction log growing exponentially larger in any scheduled reports or extract load transform (ETL) tasks.
Please share anything you can as well. Thanks.
user defined function
top 10 things not to do
Feb 09, 2010 03:59 PM|Nick Bin|LINK
Your explanation does not satisfy the title. Please change the title or insert more subsequent "things".
Nonetheless, thanks for the tips, joe!
Feb 16, 2010 03:26 PM|joeastral|LINK
Sorry, I was hoping someone else would provide numbers 4 - 10. Always learning...
Feb 19, 2010 07:17 AM|TATWORTH|LINK
5) Do not have a stored procedure that just EXECUTES some TSQL. I have seen this and it is just wrong!
6) Consider grouping your stored procedures either by domain (SQL2005 and later) or by a prefix that allows allows you to set permissions by wildcard.
7) Assign EXECUTE permissions to a role which can then be assigned to one or more user accounts.
Feb 19, 2010 07:19 AM|TATWORTH|LINK
Here is an example of setting permissions by wildcard:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: Clive Chinery
-- Create date: 11Nov2007
-- Description: Set Permissions
-- Copyright (C) 2000 to 2009 Clive Chinery
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the
-- Free Software Foundation, Inc., 59 Temple Place, Suite 330,
-- Boston, MA 02111-1307 USA
ALTER PROCEDURE [dbo].[asp_dbPermissions]
@Execute BIT = 1, -- Default is Execute
@Print BIT = 0 -- Default is No print
SET NOCOUNT ON;
DECLARE @ROLENAME VARCHAR(50)
SET @ROLENAME = 'WebRole' -- Change WebRole to your desired database RoleName
DECLARE xArgs CURSOR FOR
FROM sysobjects O
WHERE O.xtype = 'P' AND O.name LIKE 'usp_%'
ORDER BY O.name
DECLARE @name VARCHAR(128)
DECLARE @exec VARCHAR(160)
FETCH xArgs INTO @name
SET @exec = 'GRANT EXECUTE ON ' + @name + ' TO ' + @ROLENAME
IF @Print = 1 PRINT @exec
IF @Execute = 1 EXEC (@EXEC)
FETCH xArgs INTO @name
---------------------- This is the end ---------------
Mar 23, 2010 12:58 PM|joeastral|LINK
Cool tips on the wildcard permissions. Thanks
Aug 31, 2010 02:52 AM|riswadkarharshad|LINK
All the mentioned tips are really helpful. Thanks for sharing :)