System performance limitations

Question

System performance limitations

SQL33

SSCrazy

Points: 2044
More actions
December 6, 2010 at 2:31 pm

#147885

I have a database set up to enable me (hopefully) to query against the output from a SYSLOG server from my webfilter into this database table so i can query the results for suspicioius search strings.
The server. database and table specs are below. At this current size I can't even query the single table on one day's worth of data without waiting for several minutes of a reply.
Is there a physical limit on table size in SQL? If not, is there something I can do (or read) to help me figure out how to improve performance on this database?
Thanks...
Windows 2003 on a
IntelRXenon 2.8 GHz with 1GB of Ram
Disk C 19G
Disk D 116 G
Database size 36G
One table with 27397524 records a 18 colums
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
MsgDatevarcharno10 yesnoyesSQL_Latin1_General_CP1_CI_AS
MsgTimevarcharno8 yesnoyesSQL_Latin1_General_CP1_CI_AS
MsgPriorityvarcharno30 yesnoyesSQL_Latin1_General_CP1_CI_AS
MsgHostnamevarcharno255 yesnoyesSQL_Latin1_General_CP1_CI_AS
Typevarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Protocolvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Timevarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Actionvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
IPvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Profilevarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
UserNamevarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Bandwidthvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
URLvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Ratingvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Durationvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Methodvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Statusvarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS
Mimevarcharno1024 yesnoyesSQL_Latin1_General_CP1_CI_AS

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 2

Given the limited physical power on the server, and the sheer size of the table, I'd be kind of surprised if it were significantly fast.

SQL table sizes are limited by the storage capacity available, not by a number of rows. There are number-of-column limits, but you're not even getting close to those.

I'd be interested in your indexing, and the actual query and query plan, but it's entirely possible you're simply outside the easy capabilities of a server with only 1 Gig of RAM.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 3

I'd tend to agree with GSquared. 1GB of RAM is tiny, and you might easily be causing yourself issues with that little memory. Between that and the disk IO, most everything will run slowly.

SQL33 SSCrazy Points: 2044 More actions · Answer 4

After reading your posts, realized I had not even set up Indexing on this database. I ran the SQL database tuner and turned indexing on for a couple of columns which immediately payed dividends in searching on them (DOAH!).

SO... my follow up questions are...

When setting up indexing, is it limited by the selected field size property settings? If so, can I downsize the fieldsize properties to enable more fields being included in the indexing scheme?

And...

Is there a tool which I could run on some sample data which would recommend the best size and datatype for each field?

Thanks...

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

27 million rows on what appears to be a single drive spindle desktop server? 1 Gig is barely enough to hold Windows in memory these days. 🙂

At a guess, you're spinning hard against pagefile and the like. As mentioned above, it's all about the indexing from here. It needs to be nearly pinpoint perfect for a desktop to run it well with that data volume.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

SQL33 (12/7/2010)
When setting up indexing, is it limited by the selected field size property settings? If so, can I downsize the fieldsize properties to enable more fields being included in the indexing scheme?

You get 900 bytes in an index worth of width, that's it. Shrink your fields down, and you get more fields in the index. There's a limit of # of columns allowed in the B-Tree as well (16 I think), but that's beyond what most people will ever need.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SQL33 SSCrazy Points: 2044 More actions · Answer 7

Is there a means to analyze the output from say a syslog output and determine the optimum data type and size the target SQL table fields should be?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

SQL33 (12/7/2010)
Is there a means to analyze the output from say a syslog output and determine the optimum data type and size the target SQL table fields should be?

That I'm aware of offhand? No. I use the Mk. 1 Eyeball software.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

GSquared SSC Guru Points: 260824 More actions · Answer 9

SQL33 (12/7/2010)
Is there a means to analyze the output from say a syslog output and determine the optimum data type and size the target SQL table fields should be?

I use:

select max(datalength(MyColumn))

from dbo.MyTable;

That'll tell you how wide the column needs to be for current data. Round up a little bit, and you'll be okay in most cases, on a table that's been in use for a while.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQL33 SSCrazy Points: 2044 More actions · Answer 10

SQL33

SSCrazy

Points: 2044

December 8, 2010 at 7:53 am

#1260108

Thanks!!!