Rob's SQL Blog

SQL Server Waits, Locks and Blocking

2008-05-17T07:30:00Z

The master.dbo.sysprocesses table in Microsoft SQL Server 2000 and SQL Server 2005 is a system table that contains information about the active server process IDs (SPIDs) that are running on SQL Server.

If you are using SQL Server 2005, you can also access this table by using the sys.sysprocesses compatibility view.

The waittype column, the lastwaittype column, the waittime column, and the waitresource column in the master.dbo.sysprocesses system table provide information about the resources that the processes are waiting on.

References:

How to monitor blocking in SQL Server 2005 and in SQL Server 2000
Blocked Process Report Event Class
Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005

Cross Posted from blogs.technet.com/rob

SQL Server and Windows Memory

2008-05-15T14:02:00Z

SQL Server 2000 Enterprise Edition introduced support for the use of Windows 2000 Address Windowing Extensions (AWE) to address 8GB of memory on Windows 2000 Advanced Server and 32GB of memory on Windows 2000 Datacentre. With AWE, SQL Server can reserve memory not in use by other applications and the OS. Each instance must statically allocate the memory it needs. AWE memory can only be used for the data cache and not for executables, drivers, DLL's, etc.

Cross Posted from blogs.technet.com/rob

Windows Memory (PAE/AWE/3GB)

2008-05-15T12:20:00Z

SQL Server 2000 Enterrise Edition introduced support for the use of Windows

Cross Posted from blogs.technet.com/rob

Oracle Unbreakable... Not a Chance !

2008-04-17T09:33:00Z

Good article here comparing the Critical Updates fixes between SQL Server and Oracle and some of the mis-information peddled in the RDBMS world. Highlights include zero SQL Server critical update vulnerabilities since September 2004, which I think is pretty impressive! http://blogs.technet.com/dataplatforminsider/archive/2008/04/14/unbreakable.aspx...(read more)

SQL Server 2000 Full-Text Issues After Failover

2008-03-12T18:00:00Z

I can't take credit for finding the solution to this as that goes to my colleague, but I thought I'd share a problem we experienced after failing over one of our production clusters last night. This is a SQL Server 2000 SP4 Enterprise Edition active/passive cluster running on Windows 2000 Advanced Server SP4. After failover the full-text functionality failed and we were unable to rebuild the catalogs or indexes as SQL Server was returning various errors. The path to the full text catalogs was OK...(read more)

SQL2K8 Launch Thoughts

2008-02-29T19:41:00Z

Been reading Dan Jones blog post regarding the delay to the launch of SQL Server 2008, and I think he makes some great points. There is obviously dissapointment from some people about the release date slipping back, but as Dan points out, a bad product will be remembered for a long time. SQL Server has built up a reputation for reliability, scalability and security over the last couple of releases. I'd rather Microsoft protected that reputation than rush something out to meet a marketing deadline...(read more)

Windows Server 2008 Clustering

2008-02-29T18:10:00Z

I've been having a look at Windows Server 2008 clustering and the first thing I can say is... wow ! Setting up clusters, adding additional nodes and setting up clustered resources is far simpler than before, and the cluster validation tool means there is no more need for the HCL. Clusters can also be configured so that the quorum is not a single point of failure, which increases resilience. The GUI is much cleaner and intuitive than previous versions of the Cluster Administrator, which makes configuration...(read more)

SQL Server 2005 Bug Report

2008-02-21T19:43:00Z

Saw this link to a SQL Server 2005 bug report on the Microsoft Connect site. I guess someone was having one of those days https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253524&wa=wsignin1.0...(read more)

SQL Server 2008 Certifications

2008-02-19T23:50:00Z

As I am currently preparing for the MCDBA to MCITP 2005 upgrade exam (70-447) it may seem a bit premature to talk about SQL Server 2008 certifications, but there is a webcast available at Trika's blog (from the Microsoft Learning team) below. http://blogs.msdn.com/trika/archive/2008/02/08/sql-live-meeting-recording.aspx Something I found surprising from the presentation is that there are just over 4,000 MCITP Database Administrators in the world compared to 150,000 MCDBA's. This may be down to the...(read more)

Indexing Strategies

2008-02-16T00:39:00Z

I attended the Scottish SQL Server User Group meeting during the week and heard a presentation from Martin Bell on his favourite DMV's. Mentioned in the discussion was sys.dm_db_index_usage_stats and that got me thinking about indexing strategies. I have been involved in performance troubleshooting databases that have used a variety of indexing stategies, ranging from none to lots of narrow indexes on practically every column ! However, there is no right and wrong indexing strategy, it depends entirely...(read more)

PFE Technical Rollups

2008-02-15T00:28:00Z

The latest Technical rollup mails from Premier Field Engineering are available here ....(read more)

Top Tips for SQL Server Clustering

2008-02-13T17:10:00Z

As part of a current project I am working on, I came across this article , which includes a tip for clustering SQL Server workgroup edition using a combination of Windows clustering and virtual server !...(read more)

SQL 2008 Resource Governor

2008-02-12T09:29:00Z

I came across this post in Andrew Fryer's blog, which has a short demo of the SQL Server 2008 resource governor in action. For me, this will be one of the main drivers for moving to SQL Server 2008. Most of the issues I come across with unresponsive SQL Server instances are caused by run-away processes or bad application code consuming all the system resources. The ability to throttle these processes and gaurantee service for other applications using the instance is very compelling and could slash...(read more)

Free SQL Server 2008 E-Learning Available

2008-02-11T15:32:00Z

There are some free e-learning tools available for SQL Server 2008 on the Microsoft E-learning site below: http://www.microsoft.com/learning/sql/2008/default.mspx There's also some free e-learning resourses for Windows Server 2008 here: http://www.microsoft.com/learning/windowsserver2008/default.mspx...(read more)

Update: Two-thirds of Oracle DBAs don't apply security patches

2008-02-08T09:50:00Z

This scary article reminded me of a conversation I had with a colleague in the middle of a team meeting recently. This centred around the perceived lack of security of SQL Server compared to Oracle and the Slammer worm was cited as an example. The damage done by slammer was entirely caused by DBA's and SysAdmins not applying critical updates on their SQL 2000 machines, allowing the worm to exploit these documented security holes. I guess that mud sticks and people remember the impact of slammer,...(read more)