helpful sql perf queries

Ian Strike has a very interesting article on some of the perf stats that sql2005 automatically maintains:

http://msdn.microsoft.com/msdnmag/issues/08/01/SqlDmvs/default.aspx

sourcevault DOES do unicode diffs

not sure how i missed this, but in the Diff window, go to Tools…Options… then in ‘Character Encodings’ section, pick UNICODE. when you refresh the diff, it’s happy--

avoid finalizers/use "using"

this is something we’ve gotten wrong a bit in places: in c#, only use a finalizer (i.e. a ~ClassName() method) if you need to clean-up unmanaged resources you’ve allocated. the reason for this is that if your class has a finalizer, the garbage collector will place it in the finalization queue and will not release it immediately. if you do have unmanaged resources and require a finalizer, implement IDisposable and do your cleanup in a shared Dispose(bool isDisposing) method. make sure to call GC.SuppressFinalize() in your Dispose() method so that if your client has already called Dispose(), your object is not placed in the finalization queue. for example:

public class ClassName : IDisposable
{

private bool m_IsDisposed;

~ClassName()
{

Dispose(false);

}

public void Dispose()
{

Dispose(true);

GC.SuppressFinalize(this);

}

public void Dispose(bool FromDisposeMethod)
{

if (!m_IsDisposed)
{

m_IsDisposed = true;

if (FromDisposeMethod)
{

//release managed resources; only do this if from Dispose method since they themselves might have been finalized

//...

}

//release unmanaged resources

//...

}

}

}

also, use "using" as much as possible. you can do this with any object that implements IDisposable:

using (MemoryStream ms = new MemoryStream())
{

//do whatever you want with ms

//...

}

using will automatically call Dispose() when the scope of this block ends, so you get very readable code and know that resources are properly released--

beware BIT columns in SQL2000

found a strange one today… if you have a BIT column and you write the following TSQL:

WHERE BitColumnName = 1

SQL Server doesn't quite do what you want it to do. rather than converting the 1 to a bit and then applying the where clause filter, it instead converts BitColumnName to an int and then applies the filter. this is problematic since you won’t be properly using indexes you have on the bit column. to avoid this, write:

WHERE BitColumnName = convert(bit, 1)

this is fixed in SQL2005--

declaring variables inside a loop in c#

interesting thread here: http://www.thescripts.com/forum/thread505814.html

in the past (c++) it was best practice to avoid declaring variables inside a loop; in c#, both of these compile to the same IL:

for (int i = 0; i < 100; i++)
{
StringBuilder sb = new StringBuilder();
}

StringBuilder sb;

for (int i = 0; i < 100; i++)
{
sb = new StringBuilder();
}

and since the first example has the StringBuilder scoped to the for loop, you can’t accidentally use it after this code block executes. so: declare variables inside loops in c#.

devscovery 2007 5/9 - 5/11

this was a great conference run by Wintellect, a training/consulting company that works very closely with msft on product development. the speakers were Wintellect’s technical leads (jeff richter, john robbins, jeff prosise, etc.), who are the authors of some of the Microsoft Press books we read, and all were excellent. topics covered included some of what’s in store for us in the near future (wpf, wcf, silverlight) and some stuff we can start taking advantage of right now (asp.net ajax extensions, asynchronous programming, debugging). the c# 3.0 language enhancements are very interesting; lots of syntactic shortcuts to reduce what you have to type that end up making things look very javascript-looking.

wpf (part of .NET 3.0)

• does not replace Winforms; for quick development of standard GUI, not-so-flashy desktop apps, Winforms will still have a place
• page layout defined in XAML
  o easy to exploit graphics and animation compabilities
  o nearly every property of every object is animatable
  o binding allows the property of an object to change as the property of another object changes
• similar structure to ASP.NET in that .xaml page containing markup has .xaml.cs code-behind page containing coding logic

BETA - silverlight (formerly WPF/E)

• cross-platform browser plugin
• "silverlight CLR" provides sandboxed capabilities (limited access to file system, networking, etc.)
• allows you to write almost-identical xaml pages as wpf apps
• v1.0 currently in beta
  o requires you to interact with xaml objects via javascript
  o v1.1 (currently in alpha) allows
  o allows you to interact with xaml object using C#
  o will likely displace Flash in Microsoft development shops

CTP - c# 3.0 (next version of the c# language)

• in general, the language is becoming more functional looking (LISP-like), and most new constructs are easy-to-write shortcuts to the actual operations that are rewritten at compile-time
  § implicitly typed local variables - allow you to say:
  
  var x = new String("hi");
  
  instead of:
  
  string x = new string("hi");
  
  which is particularly useful when you are instantiating a complex type
  
• extension methods - you can define a static method in a static class with new "this" keyword in parameters as follows:
  
  public static int StringToIntFunction(this string s) {...do something...}
  
  and then this function is available in instance-syntax to any String object, so you can say:
  
  string s = "hi";int i = s.StringToIntFunction();
  
• anonymous types - compiler will generate a type name for you, with specified property accessors:
  
  var someEmployee = new { Name = "MyName", Title = "Developer" }
  
  and you can then call someEmployee.Name and someEmployee.Title as if you had created an Employee class and specified these properties
  
• LINQ (Language INtegrated Queries) - new constructs allow for T-SQL-like syntax for IEnumerable interactions:int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };var lowNums = from n in numbers where n < products =" GetProductList();var" productinfos =" from" price =" p.UnitPrice};" href="http://download.microsoft.com/download/5/8/6/5868081c-68aa-40de-9a45-a3803d8134b8/csharp_3.0_specification.doc">http://download.microsoft.com/download/5/8/6/5868081c-68aa-40de-9a45-a3803d8134b8/csharp_3.0_specification.doc

asp.net ajax extensions - http://ajax.asp.net/

• we can and should start using this NOW! if you see something that will speed development time or provide richer user experience, use it!
• the asp.net ajax control toolkit contains lots of widgets that use the ajax extensions and is open-sourced
• tag allows you to specify path the your webservice; this generates a javascript-callable wrapper for your webservice methods, so you get real nice webservice.methodName() syntax on webservice calls
• JSON (JavaScript Object Notation), which is a much less-verbose SOAP alternative we should also be using already

threading and asynchronous programming model

• as much as possible, use beginread()/endread() instead of read(), etc.
• can create UI issues and is a bit more difficult model to program using (since everything is done with callbacks), but keeps threadpool lean and mean
• use Interlocked class for thread-safe variable increment/decrement

debugging, unit testing, and perf tuning

• VSTS provides almost all the necessary tools
• UI automation testing in .NET 3.0 provides APIs for driving Winform and wpf apps
• developers average 1 bug for every 10 lines of code; with unit testing and code-coverage averaging 80-90% coverage, ratio moves toward 1/1000
• beware of finalizers (cause objects to be moved to GC finalizer queue, which is not cleaned until next GC run)

tools

• BETA - Blend (formerly Expressions)
  o Flash-like IDE for creating xaml for animations
  o release versions due later this year
• CTP – “Orcas” (next version of Visual Studio)
  o project types for Silverlight apps
  o intellisense for xaml and javascript
  o release versions due later this year
  o reflector – FREE - see c# code for .NET library
  o http://www.aisto.com/roeder/dotnet/
• web development helper – FREE - firebug-like plugin for IE
  o http://projects.nikhilk.net/Projects/WebDevHelper.aspx
• .net memory profiler - awesome tool to analyze .net memory usage
  o http://www.memprofiler.com/
  

cool way to use COALESCE to return comma-delimited list in one-to-many relationship

many times when you have a one-to-many relationship, you want to return a comma-delimited list of the “many” (for example, a list of all the subjects a tutor teaches). so typically your one-to-many join in your query would produce a result set such as {UserId, SubjectId}, and then you would parse this list in application code. a neat approach i saw an example of today is to use sql’s COALESCE function with a bit of string magic to get the same comma-delimited list directly from the database (COALESCE returns the first non-null expression among its arguments). so, if you wanted to return the list subjects that userId 1 teaches, you would have:

declare @SubjectList varchar(2048)

SELECT @SubjectList =
COALESCE(@SubjectList + ', ', '') + pg.GroupName

FROM TableGroups pg

SELECT IsNull(@SubjectList, '')

you can then put this logic in a UDF and call it from a select statement. for more details, see here: http://www.sqlteam.com/item.asp?ItemID=2368

coding standards

having standards makes it much easier to read code you didn’t write, so i do as much as i can to encourage adherence to the list here.

Definitions:

• “UpperCamelCased” = capitalize first letter of each keyword (.NET style); do not use underscores)
• “lowerCamelCased” = captialize first letter of each keyword except the first (java-style); do not user underscores
• “Hungarian Notated” = prefix variable name with indicator of its type (i.e. string sMyString or strMyString)

C#: (in general, follow Microsoft .NET standards)

Functions

• Public functions “UpperCamelCased”
• Private functions “lowerCamelCased”
• Parameters “UpperCamelCased”

Variables

• Module-level variables begin “m_”
• Local function variables either:
  o “lowerCamelCased”
  o Keywords separated with “_”
  o Prefixed with “tmp_”
  o “Hungarian Notated”

Enums

• “UpperCamelCased”
• Type name pluralized (i.e. “MyItems”, not “MyItem”)

Constants

• “UPPER_CASE” (all capitalized; keywords separated with “_”)

For example:

public class MyClass
{

public const THIS_IS_MY_CONSTANT = 0;

public enum MyItems
{

None = 0
,SomeItem = 1
,AnotherItem = 2

}

private string m_MyVariable;

public void TellMeEverything(string Input1)
{

bool localVar = false;
bool local_var = false;
bool tmp_LocalVar = false;
bool bLocalVar = false;

}

private void tellYouNothing(string Input1)
{

}

}

Database Tables

• Table names “UpperCamelCased_WithAnyExtras” (capitalize first letter of each key word and use a single “_” character as desired)
• Column names “UpperCamelCased”
• T-SQL keywords “UPPERCASE” (all capitalized)
• T-SQL Datatypes “alllowercase” (do not capitalize any character)

For example:

CREATE TABLE Groups
(
GroupId int
,GroupName varchar(50)
)

Database Sprocs

• Sproc names “UpperCamelCased”
• Separate input parameters or returned columns with comma on line of subsequent parameter or column name
• Clearly separate SELECT/FROM/WHERE or UPDATE/SET/WHERE clauses using carriage returns or tabs

For example:

CREATE PROCEDURE UpdateUser

@UserId int
,@StatusId int
,@UpdateByUserId int

AS

UPDATE Users

SET StatusId = @StatusId
,ChangedBy = @UpdateByUserId

WHERE UserId = @UserId

GO

great WPF book

Adam Nathan’s Windows Presentation Foundation Unleashed (WPF) - http://www.amazon.com/exec/obidos/ASIN/0672328917 is a fantastic WPF book, full color, thick glossy paper, and lots of great practical examples--

writing OLAP queries - best practices

this is a list of some query tuning best practices i’ve compiled, with the general the goal being to avoid the problems of inefficient report queries that take a ton of time to execute.

• begin each sproc with "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED". this allows you to avoid NOLOCK hints within the query and ensures you don't accidentally forget one
• write the query one join at a time
• turn on ‘Show Execution Plan’ and study the plan with each join you add
  o look for the item taking the greatest % of execution time
  o check for thick lines (lots of data)
  o check for table scans/bookmark lookups
  
  
  
• turn on 'Set Statistics Time' and 'Set statistics IO' from the Tools...Options...Connection Properties menu
  o look for extremely high logical reads (in general, < 10,000 logical reads per table is ok)
  o total execution time for a query of average complexity on a server under average load should not exceed 60 seconds for one day's worth of data
  
  
  
• MAKE USE OF INDEXES! if you expected an index to be used that wasn't, figure out why
• it could be that you are missing columns in your join; for example, an index on Table1 on presenceid,userid,starttime will not be used for "select starttime from Table1 where presenceid = sp.presenceid", but will be used for "select starttime from Table1 where presenceid = sp.presenceid and userid = sl.userid"
• if there is no way to make use of an existing index, request that a new one be added or add columns to an existing one
• use a "covering index" to avoid bookmark lookups
  o a covering index is an index that is both for the seek (where/join clause) and the selected data (select clause)
  o include the columns in your where/join clause first, then the columns you are selecting. for example, with an index on Table1 on presenceid,userid,starttime, no bookmark lookup will be needed when you "select starttime from Table1 where presenceid = sp.presenceid and userid = sl.userid", since presenceid and userid are in the where/join clause and starttime is in the select clause
  o in general, make the covering index as wide as you need to avoid bookmark lookups (the exception to this is extremely wide varchar columns that will inflate the size of your index too much)
  
  
  
• if you still can't get the query execution time down, figure out another way to get the same data
• use different tables/joins
• use "EXISTS" instead of join
• use subquery instead of join
• use "=" not "<>", and "NOT EXISTS" instead of "NOT IN", whenever possible
• follow examples in existing sprocs that perform well

lots of other good tips here: http://www.sql-server-performance.com/transact_sql.asp

SCOPE_IDENTITY(), @@IDENTITY, and IDENT_CURRENT('tablename')

this came up today, want to make sure everyone understands how these work:

• SCOPE_IDENTITY() returns the value of the Identity column for the last insert, for the current session (i.e. sproc calling SCOPE_IDENTITY()) AND current scope (i.e. trigger inserts caused by your insert are not considered)
• @@IDENTITY returns the value of the Identity column for the last insert, for the current session but REGARDLESS of scope (i.e. trigger inserts caused by your insert are considered)
• IDENT_CURRENT('tablename') returns the value of the Identity for the last insert, REGARDLESS of session or scope

so in general, you probably want to be using SCOPE_IDENTITY() to get the last inserted value for an Identity column. if there are no triggers on the table being inserted to, @@IDENTITY and SCOPE_IDENTITY() are identical, but if a trigger is later added your sproc will need to be changed. you almost never want to use IDENT_CURRENT(‘tablename’), since the current value can easily change between the time you make your call to this function and you do something with the result--