Opening Firewall for SQL Server allowing TCP access

From Microsoft TechNext

When you can't access a remote sql server, always change the firewall 1st.
the confirm what the sa / user password.

To open a port in the Windows firewall for TCP access

1. On the Start menu, click Run, type WF.msc, and then click OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3. In the Rule Type dialog box, select Port, and then click Next.
4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7. In the Name dialog box, type a name and description for this rule, and then click Finish.

How to drop linked Server from SQL Server 2008

Another task I can't seem to remember.

Two useful system stored procedures showing linked servers in SQL 2005/2008


exec sp_helpserver
exec sp_linkedservers

I had trouble removing a linked server because I forgot to remove all of the logins.
(seems like removing a linked server should cascade remove the associated logins)

exec sp_droplinkedsrvlogin 'FUSIONRIS','sa'
exec sp_droplinkedsrvlogin 'FUSIONRIS',NULL
exec sp_dropserver 'FUSIONRIS'

Use while loop through table

Needed a solution to loop through a sql resultset without a temp table / table variable / cursor.
Found this question: http://stackoverflow.com/q/1578198/44597

My answer: http://stackoverflow.com/questions/1578198/can-i-loop-through-a-table-variable-in-t-sql/7417780#7417780

declare @id int

        SELECT @id = min(fPat.PatientID)
        FROM tbPatients fPat
        WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)

while @id is not null
begin
    SELECT fPat.PatientID, fPat.InsNotes
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0) AND fPat.PatientID=@id
    
    SELECT @id = min(fPat.PatientID)
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)AND fPat.PatientID>@id
        
end    

Multiple Cursor Example

DECLARE @IPaddress nvarchar(40)
DECLARE @PartnerName nvarchar(40)
DECLARE @AllMessageCodes nvarchar(200)
DECLARE @MessageCode nvarchar(10)

DECLARE @Partners TABLE
(
IPAddress nvarchar(40),
PartnerName nvarchar(40),
AllMessageCodes nvarchar(200)
)


DECLARE cur CURSOR FOR
select distinct p.IPaddress, p.PartnerName
from tbHL7Partners p
inner join tbHL7MessageMapHeader MMH on p.PartnerID = MMH.PartnerID
inner join tbHL7MessageMapDetail MMD on MMH.MsgMapHeaderID = MMD.MsgMapHeaderID
inner join tbcdHL7Messages M on MMD.MessageID = M.MessageID
where p.PartnerDeleted = '0' and mmh.Active = '1' and mmd.[On] = '1'

OPEN cur
FETCH NEXT FROM cur INTO @IPaddress, @PartnerName
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @AllMessageCodes=''

DECLARE curMessages CURSOR FOR
select M.MessageCode
from tbHL7Partners p
inner join tbHL7MessageMapHeader MMH on p.PartnerID = MMH.PartnerID
inner join tbHL7MessageMapDetail MMD on MMH.MsgMapHeaderID = MMD.MsgMapHeaderID
inner join tbcdHL7Messages M on MMD.MessageID = M.MessageID
where p.PartnerDeleted = '0' and mmh.Active = '1' and mmd.[On] = '1'
and p.IPaddress =@IPaddress and p.PartnerName=@PartnerName
GROUP BY p.IPaddress, p.PartnerName, M.MessageCode

OPEN curMessages
FETCH NEXT FROM curMessages INTO @MessageCode
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @AllMessageCodes = @AllMessageCodes + ' ' + @MessageCode
--SELECT @MessageCode

FETCH NEXT FROM curMessages INTO @MessageCode
END
CLOSE curMessages
DEALLOCATE curMessages

INSERT INTO @Partners(IPAddress, PartnerName, AllMessageCodes)
SELECT @IPaddress, @PartnerName, @AllMessageCodes

FETCH NEXT FROM cur INTO @IPaddress, @PartnerName
END
CLOSE cur
DEALLOCATE cur

INSERT IDENTITY syntax

Piece of SQL knowledge that I can't seem to keep in my brain...

SET IDENTITY_INSERT table ON

SET IDENTITY_INSERT table OFF


Now to Reseed the Identity column in a table


DBCC CHECKIDENT (table, reseed, newseedvalue)

Query tables containing same column name

I use this type of query all the time...

SELECT o.name, o.xtype, c.name, c.length, c.type
FROM sysobjects o
INNER JOIN syscolumns c on o.id = c.id
WHERE ((c.name ='CPTCode' AND o.xtype='U') OR o.name = 'tbcdCPTCodes' and c.name ='Code')
ORDER by o.name

Slow Text queries in SQL Server 2008

From StackOverflow.Net   (http://blog.stackoverflow.com/2008/10/adde-a-second-server/)

   From the article:

      While many queries are faster under SQL Server 2008, and the tooling is dramatically and indisputably better (intellisense for queries!), there is one downside for us: SQL Server 2008 is slower at full-text search operations than SQL 2005. That’s kind of a bummer because we rely heavily on full text. I saw a glimpse of this in my initial testing, when I had both servers up side-by-side with the same data, running the same queries, to see how much faster it would be (new versions are supposed to be faster, right?). My experiments showed that any full-text queries I tried were inexplicably slower on 2008, but we thought it had to do with different query plans, and was something we could work around.

   StackOverflow admin tried the workaround fix shown below but haven’t seen much improvement .

  

http://sqldev.wordpress.com/2008/09/16/sql-server-2008-full-text-slowness/

  

From the article:

It appears to happen if:

• You have AUTO tracking on your full text indexes; although we changed it to manual and still had this issue.
• You experience Full-text queries taking a long time to execute; normally when updates are happening at the same time so you might only see this in production.
• One or more of your queries are complicated or take some time to complete.

You can check to see if your system is slow due to this by:

• SELECT * FROM sys.dm_os_wait_stats statement , it shows very high wait times some of the locks.
• Running Sp_who2; it should consistently show that the full-text gather is blocking full-text queries and, in turn, is being blocked by the queries.

The current work around for this issue is to use a global trace flag microsoft kindly enabled on the RTM build.  To use this, type the following Transact-SQL statement:

DBCC TRACEON (7646, -1)

This has a minor side-effect.. which may or may not be important in your organisation. Certainly in ours, it is not. Your full text result may be ever so slightly out of date – e.g. you might return or not return a particular document from the index. However, this issue relates to the dirtyness of an index in terms of 10’s of milliseconds. Important? Unlikely….

         

    I tested the workaround for the fulltext query fix.

    On a large client DB, I ran the following query:

UPDATE tbExams

SET BusinessNotes=''

WHERE ExamID=

  Several times and the average execution time 3-4 secs

I applied DBCC TRACEON (7646, -1)

 

And then I ran the query again against different exams and the execution time dropped to 0-1 secs

Additional:  SQL Server 2008 must have AWE enabled to use all of the available memory the OS reports.
                   I assume this is necessary for 32/64 bit.

SQL SERVER – Get Last Running Query Based on SPID

Article link

SQL SERVER – Get Last Running Query Based on SPID

July 19, 2009 by pinaldave

We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.

To know which sessions are running currently, run the following command:

SELECT @@SPID
GO

In our case, we got SPID 57, which means the session that is running this command has ID of 57.

Now, let us open another session and run the same command. Here we get different IDs for different sessions.

In our case, we got SPID 61. Please note here that this ID may or may not be sequential.

In session with SPID 61, we will run any query. In session with SPID 57, we will see which query was run in session with SPID 61.

Let us run a simple SELECT statement in session with SPID 61 and in session with SPID 57 run the following command.

DBCC INPUTBUFFER(61)
GO

Now, here in DBCC command we have passed the SPID of previous session; we will see the text below. The following image illustrates that we get the latest run query in our input buffer.

There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO


The following image portrays that we get the latest run query in our input buffer.

Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.
There is one more way to achieve the same thing – using function fn_get_sql

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM ::fn_get_sql(@sqltext)
GO

All the three methods are same but I always prefer method 2 where I have used sys.sysprocesses.
Today, we have explored a very simple topic. Let me know if you find it useful.

Reference : Pinal Dave (http://blog.sqlauthority.com)

Show Stats and Execute plan in query window

The results of these commands are displayed after a query is executed from Query Analyzer, generally after the results of the query are displayed. They include:

• SET SHOWPLAN_TEXT ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run.
• SET SHOWPLAN_ALL ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run, plus additional information, such as the estimated number of rows, I/O, CPU, and the average size of a the query.
• SET STATISTICS IO ON: Shows the number of scans, logical reads, and physical reads performed during the query. Returns actual data based on a query that has run.
• SET STATISTICS TIME ON: Shows the amount of time (in milliseconds) needed to parse, compile, and execute a query. Returns actual data based on a query that has run.
• SET STATISTICS PROFILE ON: Shows a recordset that represents a profile of the query execution. Returns actual data based on a query that has run.

You will not want to run the first two commands listed above at the same time as the others because the first two commands are based on estimated data, while the last three are based on real data.
If you are using SQL Server 2000, using these commands are less needed as you can get all of the same type of data other ways from within Query Analyzer.

View TOP 20 expense queries on SQL server

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

Optimizing Stored Procedures To Avoid Recompiles

Reposted from http://www.sqlservercentral.com/articles/Performance+Tuning/ospr/666/    
   

By Randy Dyess, 2002/02/26

One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored
procedures. Recently started looking at this issue where I worked and stored procedures gathered from other clients and
I noticed that there is an issue with SQL developers not taking recompiles into account when they create new code. When
a stored procedure recompiles it will place a compile lock on the objects referenced by the stored procedure and if there
are enough stored procedure recompiles the database may experience blocking. While all databases will experience stored
procedure recompiles as a normal matter of database operations, it is when a stored procedure recompiles with every run
that a database administrator or Transact-SQL developer needs to look out for and determine a remedy.  A database getting
thousands of recompiles an hour will suffer in performance and show short term blocking that will affect the database
users. Will the query optimizer in SQL Server 2000 seems to have been greatly enhanced in its ability to reuse cached
procedure plans, a few of the following problems will still show up in SQL Server 2000.

Reasons stored procedures recompile
Stored procedures will normally recompile before execution for a number of reasons.  Dropping and recreating the stored
procedure, using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement, change the schema of any
referenced objects, running the sp_recompile system stored procedure against a table referenced by the stored procedure,
restoring the database containing the stored procedure or any object referenced by the stored procedure, or the stored
procedures plan dropping from the cache. While these recompilations are normal and cannot be helped, DBAs and developers
should not assume that all stored procedure recompiles are for normal reasons and should take a proactive approach to
determine if they have a recompile problem.

Using Profiler to capture recompiles
With the use of SQL Profiler it is very easy for a DBA or developer to determine if a stored procedure abnormally
recompiles before it is ever put into a test or production environment. It is also very easy for production DBAs to
determine if they have a problem with stored procedures already in production.

To determine if you have a problem with existing stored procedures or a specific stored procedure:
1. Start Profiler
2. Start a new trace
3. Connect to your server
4. On the General Tab, give the trace a name
5. On the Events Tab remove all default events and add SP:Recompile, SP:Starting, and SP:Completed under Stored
 Procedure events. If you want to determine the statement that causes the recompile also add SP:StmtStarting and
 SP:StmtCompleted to the selection.
6. You can leave the data columns as is or change them as you see fit. You can also leave the trace without filters,
 but stored procedures run by replication may tend to clutter your trace. If you tracing only one stored procedure,
 you can filter by the stored procedure name under the Text-Like filter.

Example of a stored procedure which will recompile on every run (7.0 and 2000)
USE pubs
GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

--create temp table
CREATE TABLE #tTemp
(a INTEGER, b INTEGER)

SELECT count(*) FROM #tTemp

--add large amount of rows to table
WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #tTemp(a) VALUES(@lngCounter)
 SET @lngCounter = @lngCounter + 1
END

--Create index on temp table
CREATE CLUSTERED INDEX ind_temp ON #tTemp(a)

SELECT count(*) FROM #tTemp
GO

EXEC dbo.spShowRecompile

Let the trace run and look for stored procedures that recompile multiple times in a row or that recompile after they have
started. To determine the statement causing the recompile look at the statement immediately before and after the recompile
if you included SP:StmtStarting and SP:StmtCompleted in your trace. Now that you have determined which stored procedures
are recompiling abnormally and which statements are causing the recompiles, we can look at ways to change the stored
procedure code to stop the recompiles.

Reducing COMPILE locks
It is considered a good practice to reference all objects in stored procedure code with the owner's name. While this will
not stop recompiles, it will stop SQL Server from placing a COMPILE lock on the procedure will it determines if all objects
referenced in the code have the same owners as the objects in the current cached procedure plan.

Example to show qualifying objects with their owners (7.0 and 2000)
USE pubs
GO

IF OBJECT_ID('dbo.spShowOwnersName') IS NOT NULL
DROP PROCEDURE dbo.spShowOwnersName
GO

CREATE PROCEDURE dbo.spShowOwnersName
AS
SELECT * FROM dbo.authors
GO

EXEC dbo.spShowOwnersName

Recompiles due to row modifications
Stored procedure will recompile is that a sufficient number of rows in a table referenced by the stored procedure has
changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics
for the table. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as
SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will
need to be recompiled.

Example of row modifications causing recompile (7.0)
IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

CREATE TABLE #Temp
(
lngID INTEGER
)

WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #Temp VALUES(@lngCounter)

 SET @lngCounter = @lngCounter + 1 
END

SELECT COUNT(*) FROM #Temp
GO.

There are several ways to avoid this specific recompile. Luckily you have two paths to take: using sp_executesql and
using the KEEPFIXED PLAN query hint that is found in SQL Server 7.0 SP3 and SQL Server 2000. You can find information
in BOL on sp_executesql and in case you don't have SQL Server 7.0 SP3 or SQL Server 2000 here is what BOL says about
KEEPFIXED PLAN: "KEEPFIXED PLAN Forces the query optimizer not to recompile a query due to changes in statistics or to
the indexed column (update, delete, or insert). Specifying KEEPFIXED PLAN ensures that a query will be recompiled only if
the schema of the underlying tables is changed or sp_recompile is executed against those tables." Books Online article on
the OPTION clause. The following examples will show you the difference both sp_executesql and KEEPFIXED PLAN will have
on avoiding stored procedure recompiles.

Example of using sp_executesql to avoid recompiles from row modifications (7.0)
IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

CREATE TABLE #Temp
(
lngID INTEGER
)

WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #Temp VALUES(@lngCounter)

 SET @lngCounter = @lngCounter + 1 
END

EXEC dbo.sp_executesql N'SELECT COUNT(*) FROM #Temp'

GO

Example of using KEEPFIXED PLAN option to avoid recompiles (7.0 SP3)
IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

CREATE TABLE #Temp
(
lngID INTEGER
)

WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #Temp VALUES(@lngCounter)

 SET @lngCounter = @lngCounter + 1 
END

SELECT COUNT(*) FROM #Temp OPTION (KEEPFIXED PLAN)
GO

Recompiles due to interleaving DDL and DML operations
Stored procedures will recompile is that the developer has place interleaving Data Definition Language operations with Data
Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the
code. The reason this happens is that the temporary objects due not exist when the initial compilation of the code takes
place so SQL Server will have to recompile the stored procedure during execution. This recompilation will take place after
the temporary object is referenced for the first time. By placing all of your temporary table creation statements together,
SQL Server can create plans for those temporary tables when one of them is referenced for the first time. This recompile
will still take place during the execution of the stored procedure, but you have cut down of the recompiles from n to two
(one for the stored procedure and one when the first reference to a temporary table is made). SQL Server will also be able
to reuse the execution plan for the stored procedure the next time the procedure is called and your recompiles will go to
zero. Remember that like permanent objects, if you change the schema of a temporary table, that change will cause the
stored procedure to recompile as well. Make all schema changes (such as index creation) right after your create table
statements and before you reference any of the temporary tables. If you take the stored procedure created during the
section on using Profiler and modify it as written below you will stop the unnecessary recompiles.

Modifications to stop the recompile (7.0 and 2000)
USE pubs
GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON

DECLARE @lngCounter INTEGER
SET @lngCounter = 1

--create temp table
CREATE TABLE #tTemp
(a INTEGER, b INTEGER)

--Create index on temp table
CREATE CLUSTERED INDEX ind_temp ON #tTemp(a)

SELECT count(*) FROM #tTemp

--add large amount of rows to table
WHILE @lngCounter < 2000
BEGIN
 INSERT INTO #tTemp(a) VALUES(@lngCounter)
 SET @lngCounter = @lngCounter + 1
END

SELECT count(*) FROM #tTemp
GO

EXEC dbo.spShowRecompile

Recompiles due to operations against temporary objects
SQL Server will recompile a stored procedure every time it is ran if any of the following conditions apply in that stored
procedure: If statements that contain the name of a temporary table refer to a table created by a calling or called stored
procedure or in a string execute by using sp_executesql or the EXECUTE statement. If any statement that contains the
name of the temporary table appear syntactically before the temporary table is created in the stored procedure or trigger.
If there are any DECLARE CURSOR statements whose SELECT statement references a temporary table. If any statements
that contain the name of a temporary table appear syntactically after a DROP TABLE against the temporary table (you
might read that DROP TABLES for temporary tables are not needed since they are dropped at the conclusion of the stored
procedure execute, but it is a good idea to drop temporary tables as you are done with them to free up system resources).
Or if any statement that creates a temporary table appear in a control-of-flow statement. By avoiding these conditions
when you create your code you can avoid needless stored procedure recompiles.

The following SET options are ON by default in SQL Server and changing the state of these options in your stored procedure
will cause the stored procedure to recompile: SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING,
SET ANSI_WARNINGS, and SET CONCAT_NULL_YIELDS_NULL. While there are not good workarounds for the first four
SET options, you can work around the last one: SET CONCAT_NULL_YIELDS_NULL by using the ISNULL function found
in Transact-SQL. By simply using the ISNULL function and setting any data that might contain a NULL to an empty string
you can avoid the setting of CONCAT_NULL_YIELDS_NULL in your stored procedure and avoid another unnecessary
stored procedure recompilation.

Example of SET CONCAT_NULL_YIELDS_NULL causing recompile (7.0 and 2000)
USE pubs
GO

IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL
DROP PROCEDURE dbo.spShowRecompile
GO

CREATE PROCEDURE dbo.spShowRecompile
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT 'Will not showup' + NULL
GO

EXEC dbo.spShowRecompile

Summary
While most of these problems occur on a larger scale in SQL Server 7.0, they can still be a problem in SQL Server 2000.
By checking for stored procedure recompiles during the development of new Transact-SQL code and running a health-check
on your existing stored procedures for recompilations, you can help to optimize your database performance by lowering the
risk of blocking while a stored procedure is being recompiled and by improving the overall performance of your stored
procedures by not having them constantly being recompiled. Keeping the above recommendations in mind as you create
your code or optimize existing code will go a long way on creating that trouble-free, lightening-fast database that all of
us would love to manage.

Knowledge Based Articles
Q243586 INF: Troubleshooting Stored Procedure Recompilation
Q276220 INF: How to Use KEEPFIXED PLAN to Disable Stored Procedure Recompilations
Q294942 PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile
Q250506 FIX: Concurrent Execution of Stored Procedure That Recompiles May Fail To Execute All Statements
Q263889 INF: SQL Blocking Due to [[COMPILE]] Locks


Copyright 2002 by Randy Dyess, All rights Reserved

Clear SQL caches

From Devx.com

When tuning SQL Server applications, a certain degree of hands-on experimenting must occur. Index options, table design, and locking options are items that can be modified to increase performance. When running a test, be sure to have SQL Server start from the same state each time. The cache (sometimes referred to as the buffer) needs to be cleared out. This prevents the data and/or execution plans from being cached, thus corrupting the next test. To clear SQL Server’s cache, run DBCC DROPCLEANBUFFERS, which clears all data from the cache. Then run DBCC FREEPROCCACHE, which clears the stored procedure cache

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Stupid SQL Tricks

http://www.sqlservercentral.com/articles/Administering/procedurecache/591/

Do not use sp_MyProc. SQL server will look for the stored procedure in Master database first. Name the procedure spMyProc and it does not happen.

Move location of SQL Server temp database

Run this query:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'H:\Data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'H:\Data\templog.ldf')
Go

Then,restart MSSQLServer service

SQLDMO cannot connect to Anaysis Services version 10.0.1600.22

Get error cannot connect to Anaysis Services version 10.0.1600.22 when trying to connect to SQL analysis server 2008

Found this posting Need AMO update:

The C# application was written for SSAS 2005 and thus references the 2005 version of Microsoft.AnalysisServices.dll (AMO). The 2008 version of AMO was just released at this link: http://www.microsoft.com/downloads/details.aspx?FamilyID=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

Scroll down to "Microsoft Analysis Management Objects" and install. The 2008 version of AMO will install to ...\Program Files\Microsoft SQL Server\100\SDK\Assemblies. In the C# solution, remove the reference to the 2005 version of this DLL and replace it with the 2008 version. BTW, this should open both 2005 and 2008 cubes.