MonitorWare Knowledge Base

I am very happy today because I get to share some rather interesting information on this particular topic for all of you for reducing (drastically in my case) the number of 114 errors with MS SQL Server 2000 (running SP4).

Yesterday I received several hundred of them (maybe thousands), and today I have received exactly 1 of them. I hope this works as well for everyone.

Like most of you, I want to tweak every little bit I can out of the performance of WinSyslog, so I started going back to basics, and reading articles on improving SQL performance (something I really haven't kept up with for about 4 years since I used SQL 7). And of course two of the most basic things in SQL definitely improved performance and removed almost all of the 114 errors.

Like most of you, I just cut and pasted the SQL statement for creating the necessary tables from the docs and created my table in SQL. I though "this is just a flat table, there's no real optimization needed other than a few indexes to improve reporting performance" - MAN WAS I WRONG.

The short and sweet is:
1. Create a NON-CLUSTERED index on the ID field - but be careful when doing this if you're using SQL Enterprise Manager since it always tries to create this as a CLUSTERED index by default.
2. Create a CLUSTERED index on the ReceivedAt field.

(see below for scripts)

For #2, in fact, you can create the clustered index on the ReceivedAt or DeviceReportedTime fields, depending on which one you usually run reports against. (Since I'm storing all my events in GMT locally to normalize the devices in different timezones, I usually use the ReceivedAt field to ensure I'm seeing things in a relatively sequential order).

NOTE: make sure you do this during a down period (or time when you don't mind losing data). Depending on your table size, this could take a long time (especially for the clustered index) and the database is unavailable during this time.

The reason want clustered for the datetime fields is because clustered is a TON better than non-clustered for searching ranges (index seek instead of a table scan - and table scans are VERY costly). Since most of my reporting is done based on data/time ranges (e.g. yesterday, last night, this week), efficiency there is paramount.

HERE'S THE SCRIPT FOR THE CLUSTERED INDEX

Code: Select all

CREATE  CLUSTERED  INDEX [idxClustReceivedAt_DEVMAPLE] ON [dbo].[SystemEvents]([ReceivedAt]) ON [PRIMARY]

You can only have one clustered index per table, so the index on the primary key (ID Field) must be non-clustered. This is perfectly okay as well since I don't think anyone uses the ID field other than for integrity or possibly sort order.

HERE'S THE SCRIPT FOR THE PRIMARY KEY AND NON_CLUSTERED INDEX

Code: Select all

ALTER TABLE [dbo].[SystemEvents] ADD
   CONSTRAINT [PK_nonClust_SystemEvents] PRIMARY KEY  NONCLUSTERED
   (
      [ID]
   )  ON [PRIMARY]

I really hope this works as well for others as it has for me. I suspect (though I haven't tested) that creating the Primary Key field is the most significant method toward preventing the 114 events.

If you want to understand this better, a couple of great articles on primary keys and indexes:

http://www.sqlservercentral.com/columnists/awarren/worstpracticesnotusingprimarykeysandclusteredindex.asp
http://www.sqlservercentral.com/columnists/dharris/indexesanoverviewandmaintenanceforperformance.asp
http://www.sql-server-performance.com/gv_index_data_structures.asp

Hi GordT,

thanks for sharing this information with us !
This sounds like a very interesting optimization, we will do some performance tests here in our labs and may create a faq article out of it !

Thanks again for sharing.

best regards,
Andre Lorbach

alorbach wrote:we will do some performance tests here in our labs and may create a faq article out of it !

Thanks again for sharing.

best regards,
Andre Lorbach

Have you ever thought about creating a SQL discussion group? I have some other maintenance scripts that I think would be generally beneficial to the user MSSQL community, as I'm sure others would have for MySQL or Access as well.

For example, I have a job that runs every hour to delete old data in batches of 10,000 records (with pauses in between to ensure table blocking only occurs for short periods of time). I found that deleting millions of records at a time (nightly), was too costly and sometimes caused the WinSyslog service to consume too much memory due to all the queueing manager caching incoming data. When this happens, the listener fails and I need to restart the service. So, doing this in smaller batches throughout the day works great, and I imagine other users would find helpful.

I can post it here if you like, but I think a separate discussion group might be better, and encourage more topic-relevant postings...

Gord,

this is just a reply to say that I am about to create the dedicated database forum that your recommended. It will be online in an hour or so.

Thanks for sharing all that information.

Rainer

That's excellent. I'll post some additional stuff tomorrow.

I'd like to hear from others whether they found that this fixed their issue or not? There have been a lot of views of this page 90+ right now, so I'd like to know if this is just on my config, or if others have the same experience.

Some further advise, now that I've been running this for a couple of weeks:

Perform a reindex periodically to optimize performance. In particular, I've created an index with fillfactor of 75% and I currently run this weekly:

Code: Select all

DBCC DBREINDEX ('dbo.SystemEvents','',75)

Of course, if you're getting a LOT of data, Winsyslog may experience problems since the database is unavailable. SQL 2000 has an alternative for "live reindexing" called DBCC DBREINDEX. I haven't used this yet, but will be experimenting with it over the next couple of days and will keep everyone posted.

GordT. wrote:SQL 2000 has an alternative for "live reindexing" called DBCC DBREINDEX. I haven't used this yet, but will be experimenting with it over the next couple of days and will keep everyone posted.

Okay. I played with the DBReindex and it doesn't work well in my case. It ran for something like 17 hours before completing (my SQL DB sits at about 80 gigs).

I believe this has to do with the fact that (while it's trying to rebuild the index), I'm still shoving just under 1 million new records in per day and deleting just under 1 million (roughly 90,000 per hour during peak). So, it has a hard time maintaining the index on a constantly changing database - not surprising, but I was hopeful.

I'm reworking my maintenance scripts right now into a mechanism I hope will work for my heaviest database, so I'm hoping to post more info (good or bad) in the near future.

It's been entertaining, and often frustrating, re-learning some of the database stuff I haven't used in about 5 years