Enable sql cache dependency in sql 2005

Here is a post about how to enable sql cache dependency in sql 2005 http://davidhayden.com/blog/dave/archive/2006/04/29/2929.aspx

SQL Server performance tuning.

My experience tells me that if your web applications (or even the windows applications) run slower, 90 percent of the time, it's because some sql queries, stored procedures, indexes on the tables are not optimized. It's very important that you can find which query, which table are running slowly.

The book "SQL Server Query Performance Tuning Distilled" by Sajal Dam is a very good book on this topic.

1. Use the sql profiler to find what queries are taking long time, but I finds out it's not very pleasant to work with the user interface of the profiler. It's especially annoying even if you click on the stop button, the events are still being logged. (The stop command sent through the user interface is taking long time because the logging thread is very busy in logging the events in the sql server. This is a typical scenario when you try to kill a thread which is in a busy while loop.) The author provides an alternative way to do this, you can use the sql profiler to create a template or open an existing template, and stop the log immediately after starting the log (You only need do this once, so it won't be too painful). You don't have to do that, this step simply helps you to prepare the sql scripts. You can use sp_trace_create, sp_trace_setevent, sp_trace_setfilter to create all the scripts manually. In the profiler, you can script the trace definition and the step to run the trace to a SQL script file by selecting File->Script Trace -> For SQL Server 2000.
2. Open the saved script file in Query Analyzer, and modify the InsertFileNameHere parameter of sp_trace_create to a meaningful filename.
3. Execute the sql script, which will start the SQL trace as a background process. It also returns the ID of the trace as traceid, which is very important and required to stop the trace later on. You can use the SELECT * FROM ::fn_trace_getinfo(default) to find the trace status.
4. Use EXEC sp_trace_setstatus traceid, 0 to stop the trace and EXEC sp_trace_setstatus 1,2 to close the trace.
5. Open the trace log file in the profiler and save it to a table, and you can query the cpu time, logical read, or anything else you selected in the profiling process.

Try to avoid sql generated names if you want to use change scripts to do the database upgrade.

Sometime, if you don't explicitly name an object when you create an object in sql server, the sql server will create a default name for you. It is not too bad if you have some sophisticated tool to migrate your database. But if you want to run your change scripts manually , it's best that you name every object you created, not relying on the system generated name.

One way to create an object in the sql server is normally dropping it firstly, that's where I got hit when relying on the system generated name.

I have an sql script like this:

IF dbo.ColumnAlreadyExists('BusinessCategory','IsActive')=1
BEGIN
ALTER TABLE BusinessCategory DROP COLUMN IsActive
END

ALTER TABLE BusinessCategory ADD IsActive Bit DEFAULT 1

It's fine when I ran it the first time, it generated the new column for me, but when I ran it again, it complains there is a default constraint object on the IsActive column which needed to be dropped firstly. The issue is the system generated a long name for my default constraint. The name is long, hard to remember, and could be changing, so it makes the changes scripts hard to write.

So, the better way is to use a named object instead like this:


ALTER TABLE BusinessCategory ADD IsActive Bit
GO
ALTER TABLE BusinessCategory ADD CONSTRAINT BusinessCategory_Default DEFAULT 1 FOR IsActive
GO

That way, you can always drop the named constraint if you want to drop it like this:

IF dbo.ColumnAlreadyExists('BusinessCategory','IsActive')=1
BEGIN
ALTER TABLE BusinessCategory DROP CONSTRAINT BusinessCategory_Default
ALTER TABLE BusinessCategory DROP COLUMN IsActive
END

Visual Studio Database Project.

Visual studio 2005 has a lot of project templates when you choose to create a new project. I used mostly library project, windows form project, web project.

The one I used today is visual studio database project. I worked on a state government project which uses a lot of tables, stored procedures.

The database project will allow me to organize all my queries, scripts, stored procedures into one project. It's very neat. Before I started to use that project, I cannot organize all my database scripts very well.  I normally keep all my scripts in one file, and copy one or another to the sql query analyzer. Then I get confused when I have a lot of scripts.

"Timeout expired" error message when the sql debugging is on.

Following yesterday's post, now, I am able to debug into the stored procedure from my windows application or web application. But after a couple of times, I start to receive annoying "Timeout expired" error message when the program tries to open a connection to the server.

Finally, I have to find another KB830118,  and the status of this issue is "This behavior is by design".  I am start to doubt if "step into stored procedure" will cause the web site hanging in debug session, what is the real use of it.

Interesting..., maybe all those KBs are existing to address those stupid bugs.

Magic EXEC SP_SDIDEBUG 'legacy_on'

In my previous job, I don't have to do too many stored procedures and those stored procedures are generally simple. The project I am working on right now involves a lot of stored procedures, and I need a good way to debug it. It's pretty easy if I am working in visual studio 2005 and SQL server 2005. Everything works right out of box.

But when I started to use visual studio 2003 to debug a stored procedure on SQL server 2005, it complained that the sp_sdidebug cannot be found. It's true, this particular debug procedure is not in the "Extended Stored Procedures" of the master database of the SQL server 2005, but it is in the master database of the SQL server 2000.

So, I moved the database to the SQL server 2000. It's getting much better, it can allow me to debug into the stored procedure from the server explorer.  It at least proved that the SQL debugger is working. But every time, when I tried to debug into the stored procedure from the vb.net code, it never stepped into the code.

I searched a lot of posts, and a lot of posts pointing to this KB328151. I tried this magic statement EXEC SP_SDIDEBUG 'legacy_on'  , no , it didn't work. I rebooted the server machine, tried this statement again, it seemed it worked finally. 

In the KB, it mentioned it's only an issue of SQL server sp3, actually, my SQL server is sp4 , and it still applys.

SQL Server 2005 Compact Edition and SQL Server 2005 Express Edition

I have been using the Express Edition for a while, but are not aware the difference between the compact edition and the express edition. Here is a white paper.