Here's another interesting post about Memory Management in Windows.

https://blogs.msdn.com/slavao/archive/2005/01/29/363181.aspx

Service Pack 4 for SQL Server 2000 has been released!

http://www.microsoft.com/sql/downloads/2000/sp4.asp

In case you guys don't know her already.
She is one of the absolute gurus in the obscure SQL Server world :-)

http://www.sqlskills.com/blogs/kimberly/

Her blog isn't updated on a daily basis but when she updates it you can be sure that it's very usefull information.

Here's an interesting article on JOIN hints.

Transact-SQL Blog: Optimizer Join Hints

I was trying to determine whether I had put all of my tables on the correct filegroup and I wanted to list this. I came up with the following query to quickly list all the needed information.

SELECT o.Name as 'Table Name', g.GroupName
FROM sysobjects o
JOIN sysindexes i on i.id = o.id
JOIN sysfilegroups g on g.groupid = i.groupid

A lot of people tend to use DBCC FREEPROCCACHE when they are performance testing procedures. This is obviously a very good habit, however, when sharing a server with other databases you are clearing the cache for all databases. You can prevent this by using DBCC FLUSHPROCINDB(dbid) which only clears the cache for a specific database.

Found this old but yet very useful article in SQL Server Magazine (InstantDoc 40925).
It is an in depth study of DBCC SQLPERF (WAITSTATS) with a lot of information on what to monitor.

There are a couple of interesting formulas in the article that came to my attention.

Initial Compiles(%) = (SQL Compilations/sec - SQL Recompilations/sec) / Batch Requests/sec
Plan Reuse (%) = (Batch Requests/sec - Initial Compiles/sec) / Batch Requests/sec

To determine the optimal Packet Size use the following formula:
Average Number of Bytes per packet = Bytes Total/sec / Packets/sec

In our journey for new questions to ask on an interview I came up with an interesting question. Although it is not too difficult there are not too many people who really know the difference between DELETE (without WHERE) and TRUNCATE.

TRUNCATE is faster than DELETE is not a complete answer (although true), but what makes it faster.

DELETE removes the rows one at a time and records this in the transaction log. TRUNCATE removes them by deallocating the data and index pages and only this action is logged.

There are a couple of things to note though:
- If a foreign key references the table TRUNCATE will not work
- If the table is publishing data for replication TRUNCATE will not work
- Delete triggers are not fired

Many people believe that TRUNCATE is not logged, this is of course not true. You can use TRUNCATE in a transaction (try using it in a transaction and rollback that transaction).

New beta's have arrived for both Visual Studio 2005 and SQL Server 2005!
Go get 'em!

Hmmm very very weird behaviour :s

We have an Execute SQL task that calls a stored procedure containing xp_cmdshell to BCP out 2 tables and some other stuff. Somewhere after the export of the second one the step reports to be completed successfully although there are a couple commands that need to be executed after the BCP (in the same stored procedure).

Apparently - still investigating though - using NO_OUTPUT solves the problem

Ok :x
We've been looking for a problem in a DTS package for 3 days with 2 people already.
I've stumbled on the following KB which explains why we were probably looking on the wrong places :(

FIX: A DTS Package That Uses Global Variables Ignores Error Message Raised by RAISERROR

Solved in a hotfix and of course in SP4 which should be arriving around June (I guess)

Time for the next tip too :)

Tip 2

What is the fiber mode used for and when should I use it?

SQL Server 7.0 and 2000, by default, run in what is called "thread mode." What this means is that SQL Server uses what are called UMS (User Mode Schedulers) threads to run user processes. SQL Server will create one UMS thread per processor, with each one taking turns running the many user processes found on a busy SQL Server. For optimum efficiency, the UMS attempts to balance the number of user processes run by each thread, which in effect tries to evenly balance all of the user processes over all the CPUs in the server.

SQL Server also has an optional mode it can run in, called fiber mode. In this case, SQL Server uses one thread per processor (like thread mode), but the difference is that multiple fibers are run within each thread. Fibers are used to assume the identity of the thread they are executing and are non-preemptive to other SQL Server threads running on the server. Think of a fiber as a "lightweight thread," which, under certain circumstances, takes less overhead than standard UMS threads to manage. Fiber mode is turned on and off using the "lightweight pooling" SQL Server configuration option. The default value is "0", which means that fiber mode is turned off.

So what does all this mean? Like everything, there are pros and cons to running in one mode over another. Generally speaking, fiber mode is only beneficial when all of the following circumstances exist:

--Two or CPUs are found on the server (the more the CPUs, the larger the benefit).

--All of the CPUS are running near maximum (95-100%) most of the time.

--There is a lot of context switching occurring on the server (as reported by the Performance Monitor System Object: Context Switches/sec. Generally speaking, more than 5,000 context switches per second is considered high.

--The server is making little or no use of distributed queries or extended stored procedures.

If all the above are true, then turning on "lightweight pooling," option in SQL Server may see a 5% or greater boost in performance.

But if the four circumstances are all not true, then turning on "lightweight pooling" could actually degrade performance. For example, if your server makes use of many distributed queries or extended stored procedures, then turning on "lightweight pooling" will definitely cause a problem because they cannot make use of fibers, which means that SQL Server will have to switch back-and- forth from fiber mode to thread mode as needed, which hurts SQL Server's performance.

Also note that some features no longer work when enabling this mode like SQLMail.

(c) http://www.sql-server-performance.com

Aaah we're going to Spain in a month or 2 with the company. This is a yearly habbit but this year 2 people of MCS will be joining us. There will be a training about SQL Server 2005 and one about Visual Studio 2005. Really looking forward of course :)

Although the "Feria" is quite nice too :p

Hmm long time ago, been really busy @ work and @ home ;)

Read about some interesting new features in SQL Server 2005.
- CTE
- CROSS/OUTER APPLY
- DELETE/UPDATE OUTPUT INTO
- INSERT WITH MERGE
- XPath Queries in XML columns
- Triggers on DDL
- ...

The list is long and very very interesting :(

I can hardly wait to use it but unfortunately the release will be around the end of the year and before companies start using it will take some time too. At home I will of course enjoy it to it's fullest :)

Debugging in a production Environment

Check out this link, the article is written by a colleague of ours and it's quite nice (kk biased information but I still think it is :p)

Over the years I've gathered a list of FAQ's about SQL Server.
I'll try to post some of them from time to time. Please note that some content might be from other sources than myself, I will try to mention them as accurate as possible!

Tip 1:
A good query to view index information (© Kimberly Tripp)

SELECT object_name(si.[id]) AS [TableName]
, rowcnt AS [Row Count]
, CASE
WHEN si.indid = 0 then 'Heap'
WHEN si.indid = 1 then 'CL'
WHEN INDEXPROPERTY ( si.[id], si.[name], 'IsAutoStatistics') = 1 THEN 'Stats-Auto'
WHEN INDEXPROPERTY ( si.[id], si.[name], 'IsHypothetical') = 1 THEN 'Stats-HIND'
WHEN INDEXPROPERTY ( si.[id], si.[name], 'IsStatistics') = 1 THEN 'Stats-User'
WHEN si.indid between 2 and 250 then 'NC ' + RIGHT('00' + convert(varchar, si.indid), 3)
ELSE 'Text/Image'
END AS [IndexType]
, si.[name] AS IndexName, si.indid
, CASE
WHEN si.indid BETWEEN 1 AND 250 AND STATS_DATE (si.[id], si.indid) < DATEADD(m, -1, getdate())
THEN '!! More than a month OLD !!'
WHEN si.indid BETWEEN 1 AND 250 AND STATS_DATE (si.[id], si.indid) < DATEADD(wk, -1, getdate())
THEN '! Within the past month !'
WHEN si.indid BETWEEN 1 AND 250 THEN 'Stats recent'
ELSE ''
END AS [Warning]
, STATS_DATE (si.[id], si.indid) AS [Last Stats Update]
FROM sysindexes AS si
WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
ORDER BY [TableName], si.[indid]

64-Bit and SP1 for Windows 2003 Server... it's coming!
click

Check out this excellent article @ MSDN:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Nice post about concurrency... be sure to read the comments too!

If you're into Reporting Services check out http://sqldbatips.com/showarticle.asp?ID=62
This is a nifty little tool that scripts your reports for deployment on other servers, it also generates a handy batchfile for the deployment.