Improving Performance

SQL Server Database Engine

Tech_Andrew


I've written a small application that uses a Microsoft Sql Server 2000 Database and Here lately i've been experiencing alot of performance problems with timeouts and data just taking too long to retreive. I was wondering what could i do to help with this.

Details:
My Program i wrote simply takes windows eventlogs and uploads them into a central database in general , i have also build a small asp.net page, where users could write out queries or save them for later use. The program is hitting our servers and it grabs them remotely, inserts into sql one by one, and clears out the logs when finished, it runs one a day, and generates about 12,000 events daily. (mostly login's and logoffs). once it starts it'll take about 2 and a half hours to finish going through everything.

Database Design...
Single Table, about 3.5 million rows now, takes up about 3 gigs of harddrive space.

Column Definitions
Name, Data Type

  • EventEntryID INT PrimaryKey(Clustered) (Auto-Increment Identity)
  • Log varchar(300)
  • Type varchar(300)
  • Date smalldatetime
  • Source varchar(300)
  • Category varchar(300)
  • Message varchar(8000)
  • EventID varchar(300)
  • UserName varchar(300)
  • Computer varchar(300)
I know the database design can probally use some work, but it's hard to do so now b/c of the massive amoutns of data without killing the transaction log.

Computer Specs:
OS: Window Server 2003
Processor: Intel 700 MHz
RAM: 1 GB

I know it's a small server, but this is pretty much the largest thing this server deals with

I've tried to monitor sql with perfmon and sql performance counters and I just don't see much of anything going wrong with it there, so i'm thinking the the hardware isn't an issue, but then again, i'm not very familar with sql.

Top

Re: Improving Performance

AKuz


2 and a half hours is a little bit excessive to insert just 12K rows. I would try to insert 12K rows from a csv file using bcp or DTS. How much time that will take Also how do you insert Do you use parameterized statements or are you issuing statements with constants, such as
INSERT INTO MyTable( columns list) VALUES(1, 'bla', bla bla', bla' ...)

Are there any triggers on your table





Top

Re: Improving Performance

AKuz

2 and a half hours is a little bit excessive to insert just 12K rows. I would try to insert 12K rows from a csv file using bcp or DTS. How much time that will take Also how do you insert Do you use parameterized statements or are you issuing statements with constants, such as
INSERT INTO MyTable( columns list) VALUES(1, 'bla', bla bla', bla' ...)

Are there any triggers on your table





Top

Re: Improving Performance

Tech_Andrew

kk, what is bcp or DTS
remeber i said i wasn't familar enough with Sql

and the inserts are parameterized

and well i'm not sure some of that time is getting the event entries remotely...

but's it's not the upload that is bothering me... it's the performance when i try to query for information.


Top

Re: Improving Performance

mcmonap

Hi Tech_Andrew,

I guess you do not have any indexes on your table, can you post some samples of the types of queries that are being executed against the data From this it might be possible to suggest some improvements in retrieving data. For example if lots of queries want to know what happened on a certain server between such and such and date a couple of indexes should have the whole thing executing more efficiently.


Top

Re: Improving Performance

Tech_Andrew

well nearly of the queries are ordered by Date DESC, to look at the most recent events first
and alot of them are asking for specific eventIDs, or IDs that are not in a set
and alot of them are searching with a LIKE clause on the message column
and alot of them are searching for a specific username
or a specific type column, which holds the (Application, System, or Security)
the source is looking for particular problems, but it isn't used as much as the rest.
the categoy isn't used very much at all

all of it is pretty muched used. i've read a little bit about query strategies but i'm not comming up with much b/c pretty much all of the data is queried for on one way or another.

My best guess at an order of importance would go like this
EventID
Date
Message
Type
Computer
Source
UserName
Category

Right now this is one index on Date, and the index is ordered by DESC.
But that still didn't improve the performance at all, or at least it didn't improve
the performance enough to where it wouldn't time out on everything.


Sample Queries

Get Yesturdays Application Errors
SELECT *
From EventLogsTable
Where Log == "Application" and Type = "Error"
Order by Date DESC

Most Event Generating Users
Select UserName, Count(*)
from EventLogsTable
Where UserName is NOT in ( ' ', 'NTAUTHORITY', 'Administrator' )
Group By Count(*)
Order by count(*) DESC

Important Event IDs
Select *
From EventLogsTable
where EventID is in ( 'Several Event IDs here' )
order by Date DESC

or just any specific peice of information to help narrow down to get to what we are looking for at that particular moment.

I was also thinking about, since most of the data is plain text, like the Message column i was wondering if enabling full text search on that column would help. i was hoping the full text catalog would help imporve the performance of it. but I don't know if the full text catalog is used only for particular queries like queries with the CONTAINS clause in it, or if the LIKE clause will go through the catalog or just if any more queries from that point the catalog is created and indexed. not sure

I hope this helps


Top

Re: Improving Performance

Tom Phillips

Install more RAM in the server. You will see a huge increase.


Top

Re: Improving Performance

Tech_Andrew

i would but installing Hardware isn't an option.. and i've already ruled the memory issues out with the use of Sql Profiler and The Perfmon.


Top

Re: Improving Performance

GlennAlanBerry

You really need to try to figure out which queries are called most often and/or are the slowest. Then look at the WHERE clause in those queries and try to get an idea which columns are most important to index. A single index is not going to be sufficient here.

Adding more or better indexes will help SELECT performance, but can hurt INSERT performance if you have too many.

It is very likely you are under memory pressure with only 1GB of RAM. You should check your Buffer cache hit ratio and Page Life Expectancy to get a better idea about that.

Do you have the data file(s) and transaction log on different disks or do you just have a single hard drive




Top

Re: Improving Performance

Tom Phillips

I find it EXTREMELY hard to believe you are not running out of memory and page swapping. 1GB for Windows & SQL is not enough to run anything of the size you describe.





Top