After the changes i made 2 weeks agoregarding queries performance, few days ago i started to note that that same page was starting to perform very bad. Within this days loading time grew from 5-10 seconds to 18-20 seconds and then to over 50 seconds (to be said that the page run over 80 queries and QoQ some queries being very big with lots of joins and sub-queries). Yes there has been added rows to particular tables that made the total number of records in the play to grow essentially. Yes the DB is under a more or less serious load. But still there aren't millions of rows used so there shouldn't happen such performance decrease.
So i started to dig. Added WITH (NOLOCK)to some SELECT's, shrinked the DB, de-fragmented and re-built the indexes, tried some optimizations, etc. But without a real success. And here i noted the following structure in the query that was making problems this time:
CONVERT(varchar(10), displayDateTime, 101) = CONVERT(varchar(10), GETDATE(), 101)
Sometimes it may be ok to use such constructions, but as a general rule, datetimeconversions are quite heavy.
So what i did was i created a new field displayDateTimeFormatedas varcharand at INSERTdrop CONVERT(varchar(10), GETDATE(), 101)into it. And the above structure now becomes:
displayDateTimeFormated = CONVERT(varchar(10), GETDATE(), 101)
Just a small change, but the speed of the query increased from 56 seconds (from my tests) to just 3 seconds.

Just another things to keep in mind.

Yesterday i got a query that was performing VERY bad. It had 4 joined tables and 4 subqueries. Nothing REALLY complicated, but it was taking 80-100 seconds to execute. That's right, about 1.5minutes!
First thing that came to my head and usually it helps was: indexes. I created them, but.... they didn't help much in this case.
So i tried to analyze execution plan. What took my attention was that one of the sub-queries had an over 20% cost. But it was a simple one so nothing really could be done there optimization wise.
Next step was to try to change the order of the tables in joins. As "base" table i made the one that had the "bad" sub-query. And - YES, this DID help! From the initial around 100 seconds, the time went down to around 5 seconds.

Lesson learned: trying to optimize a query, pay attention to joined tables order!

This hsould help you determine on which port(s) connections were coming through from the client to the database server:

SELECT c.session_id, c.local_tcp_port, s.login_name, s.host_name, s.program_name

FROM sys.dm_exec_connections AS c INNER JOIN

            sys.dm_exec_sessions AS s on c.session_id = s.session_id

WHERE c.local_tcp_port <> 1433

Been trying to run a quite big sql script on MS SQL 2005 getting Exception of type System.OutOfMemoryException was thrown. (mscorlib)
The solution is simple, run the following CMD command:

sqlcmd -S YOURSQLSERVER\INSTANCENAME -U YOURUSERNAME -P YOURPASSWORD -d YOURDATABASE -i "c:\somescript.sql"

Note that the parameters like -U and -P are case sensitive!

Retrieving information from a history table for a given point in time is a common data task. For example, using a table that contains all historical prices,
what where the prices of all products as they were 30 days ago? There are a number of ways to different methods to obtain this information from the audit table.

Sql-Server-Performance.com had a nice article in March comparing 5 ways of Retrieving Data from an Audit Table:

1. Using a subquery to determine which record for each product is the correct one for 30 days ago. The subquery selects the maximum DateEntered that is less than 30 days for each product and is refered as subquery(max).
2. Using a subquery to determine which record for each product is the correct one for 30 days ago. The subquery selects the top 1 DateEntered that is less than 30 days, ordering by DateEntered descending and is refered as subquery(top).
3. Using cross apply. The Products table is cross applied against the most recent record of PriceHistories that is older than 30 days.
4. Using string manipulation. Concatenate the DateEntered with the price and use this concatenation to determine the greatest DateEntered that is less than 30 days. Return the substring of the concatenation that represents only the price.
5. Using the rownumber function in a common table expression to select the first record that older than 30 days for each product, ordered by DateEntered.

The results are quite interesting. While i subjectively was thinking that the RowNumber method would be better than the Subqueries, it isn't the case and cross apply normally seem to be the best match.

There often appear questions about which is better and of course the answer is: It Depends.
Bellow is a summary comparing Table Variables and Temporary Tables side by side.

Summary

Feature	Table Variables	Temporary Tables
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement. SELECT INTO statement.
Table name	Maximum 128 characters.	Maximum 116 characters.
Column data types	Can use user-defined data types. Can use XML collections.	User-defined data types and XML collections must be in tempdb to use.
Collation	String columns inherit collation from current database.	String columns inherit collation from tempdb database.
Indexes	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL (indexes, columns)	Statements are not allowed.	Statements are allowed.
Data insertion	INSERT statement (SQL 2000: cannot use INSERT/EXEC).	INSERT statement, including INSERT/EXEC. SELECT INTO statement.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	The SET IDENTITY_INSERT statement is not supported.	The SET IDENTITY_INSERT statement is supported.
Truncate table	Not allowed.	Allowed.
Destruction	Automatically at the end of the batch.	Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Transactions	Last only for length of update against the table variable. Uses less than temporary tables.	Last for the length of the transaction. Uses more than table variables.
Stored procedure recompilations	Not applicable.	Creating temp table and data inserts cause procedure recompilations.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).
Statistics	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.	Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Pass to stored procedures	SQL 2008 only, with predefined user-defined table type.	Not allowed to pass, but they are still in scope to nested procedures.
Explicitly named objects (indexes, constraints).	Not allowed.	Allowed, but be aware of multi-user issues.
Dynamic SQL	Must declare table variable inside the dynamic SQL.	Can use temporary tables created prior to calling the dynamic sql.

By Larry Gonick