SQL Server performance problem!

[hishamfangs]

Hi guys!

I'm facing a problem and I can't quite figure it out!
I have created a table on SQL Server 2000 to store call records. We get
about 250,000 calls a day, and the most frequently used query retrieves
all the calls records for the day and groups them by destination, so
the query would return something like this:

Destination Calls Minutes

Austria - Mobile 1,490 2,190.97
Austria - Vienna 9 8.90
Brazil 6 0.73
Brazil - Belo Horizonte 3 1.22
Brazil - Mobile 2 0.00
Canada 175 161.57
China 4 0.52
China - Mobile 1 6.32
Denmark - Mobile 1 0.00
Dominican Republic 49 6.48
Dominican Republic - Mobile 3 0.00
Egypt 112 0.00
Egypt - Cairo 3 3.48
Egypt - Mobile 5 13.08
El Salvador 4,148 5,668.23
France 3 0.00
France - Mobile 5,778 3,458.88
Germany - Mobile - D2 15,155 11,588.58
Germany - Mobile - E+ 19,906 13,462.65
Germany - Mobile - E2 1,706 1,100.78
Germany - Mobile - Quam 1 0.28
Iran - Mobile 129,040 25,454.00
As you can see in any given day there are easily 250,000 records in
that table. The query we run to return the previous result usually
takes up to 2 minutes in some occasions, but usually it is around 1
minute!

I'm thinking this is an unusally long amount of time, and in a few
months the number of records will qaudruple, so the problem will be
even worse! This table was originally a few seperate tables and are
accessed through joins, but I eliminated that since we wanted to boost
performance and only inserted a minimalist amount of data into a single
table just to return the above query, as expected, it boosted
performance down to 1 minute-2 minutes, which was alot better than
before, but I still believe that it can be better! I don't believe that
it should take sql server 2 minutes to return 250,000 records grouped
into 50! This query is ever only run once at a time, since there are
only 5 users on the entire system.

Now, this table is indexed by date and by record id (primary key),
and the server is running on a Compaq dual processor 950 mhz, with 2
Gig of RAM, with 4 level 5 raid disks.

I rebuild the indexes every night, I defragment every weekend, and the
data is stored on 4 raid level 5's, so I/O should be alot quicker.
I thought that I should probably divide the database into 3 files, with
the indexes in one file, and the data in the other 2, and put each one
on a seperate drive, and eventually I will do that, but I believe that
other than that there must be another way to boost performance, and fix
whatever reason the server chugs away on that query!!

Any ideas anyone?? The record numbers are increasing steadily everyday
and if it keeps going the way it is this query might take up to 7
minutes to run in a couple months time, which would be a disaster!

Please help with any thoughts ot suggestions!!

Thank you!

[Erland Sommarskog]

(hi*********@gmail.com) writes:

I'm thinking this is an unusally long amount of time, and in a few
months the number of records will qaudruple, so the problem will be
even worse! This table was originally a few seperate tables and are
accessed through joins, but I eliminated that since we wanted to boost
performance and only inserted a minimalist amount of data into a single
table just to return the above query, as expected, it boosted
performance down to 1 minute-2 minutes, which was alot better than
before, but I still believe that it can be better! I don't believe that
it should take sql server 2 minutes to return 250,000 records grouped
into 50! This query is ever only run once at a time, since there are
only 5 users on the entire system.
As I understand your description, SQL Server will have to scan all
those 250000 rows to get the data.

But it would really help if you posted the CREATE TABLE and CREATE
INDEX statements for your table, as well as the query you use.
Now, this table is indexed by date and by record id (primary key),
and the server is running on a Compaq dual processor 950 mhz, with 2
Gig of RAM, with 4 level 5 raid disks.

Hm, isn't that CPU a tad slow by today's standards?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hisham]

Yup, here is the table description
//------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[tblVoIPTempCallDetails] (
[vcdId] [int] NOT NULL ,
[acctid] [int] NULL ,
[userId] [int] NULL ,
[userFN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[userLN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[userUnixName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[custId] [int] NULL ,
[Organization] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[callCost] [numeric](18, 0) NULL ,
[callRate] [smallmoney] NULL ,
[callDiscount] [smallmoney] NULL ,
[callAdjRate] [smallmoney] NULL ,
[vdsId] [int] NULL ,
[vdsDialCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vdsName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vdsDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[vcsId] [int] NULL ,
[vcsName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcsColor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcdStartDt] [datetime] NULL ,
[vcdBillingSec] [int] NULL ,
[vcdAccountcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[vcdSrcNum] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcdSrcName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vcdSrcChannel] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[vcdDstPrefix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcdDstNum] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcdDstChannel] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[callGK] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vrName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vrDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[vrUserId] [int] NULL ,
[vtcMonth] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vtcDay] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vtcActive] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblVoIPTempCallDetails] WITH NOCHECK ADD
CONSTRAINT [PK_tblVoIPTempCallDetails] PRIMARY KEY CLUSTERED
(
[vcdId]
) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblVoIPTempCallDetails] ADD
CONSTRAINT [DF_tblVoIPTempCallDetails_callCost] DEFAULT (0) FOR
[callCost],
CONSTRAINT [DF_tblVoIPTempCallDetails_callRate] DEFAULT (0) FOR
[callRate],
CONSTRAINT [DF_tblVoIPTempCallDetails_callDiscount] DEFAULT (0) FOR
[callDiscount],
CONSTRAINT [DF_tblVoIPTempCallDetails_callAdjRate] DEFAULT (0) FOR
[callAdjRate],
CONSTRAINT [DF_tblVoIPTempCallDetails_vtcActive] DEFAULT (1) FOR
[vtcActive]
GO

CREATE INDEX [IX_tblVoIPTempCallDetails] ON
[dbo].[tblVoIPTempCallDetails]([vcdStartDt] DESC ) WITH FILLFACTOR =
70 ON [PRIMARY]
GO

CREATE INDEX [IX_tblVoIPTempCallDetails_1] ON
[dbo].[tblVoIPTempCallDetails]([acctid]) WITH FILLFACTOR = 70 ON
[PRIMARY]
GO
//------------------------------------------------------------------------------------------------------
and the query I use is the following:

select
vdsName + isnull(' - '+vdsDescription,'') callSummaryName,
min(vcdStartDt) callMinDt,
max(vcdStartDt) callMaxDt,
count(*) callTotalCount,
isnull(sum(vcdBillingSec),0) callTotalSec,
from tblVoIPTempCalldetails where vcdStartDt between '6/20/2005
0:0:00.01' and '6/20/2005 23:59:59.59'

thanx for all your help!

[--CELKO--]

Hae you thought about looking at StreamBase, Kx or the other database
products that are designed to handle streaming data? They keep up with
things like high volume phone calls and stock mrket quotes.

[Gert-Jan Strik]

Hisham,

Based on your narrative and DDL, I am assuming the following numbers:
a) "250,000 rows per day" and "in a few months the number of records
will qaudruple": that suggests your table currently holds about one
month of data, so approximately 7.5 million.

b) Assuming an average length for variable length columns of 50% of the
defined length, the average row would occupy 576+6+5 = 587 bytes. A fill
factor of 70 suggests you table contains approximately 777,000 pages
(about 6 GB) (excluding nonclustered indexes).

c) There is no covering index for your query. This basically means that
in this case, SQL-Server will have to scan the entire table, except for
the cached pages. Let's assume that 1.5 GB of your internal memory
already holds data of the tblVoIPTempCallDetails. This still leaves
585,000 pages to be read.

d) Assuming no other activity and that all "4 level 5 raid" drives are
10K RPM, then 1 page read could take 1 ms.

If all the assumptions above are correct (which is highly unlikely), and
SQL-Server would really perform a clustered index scan, then the query
would take some 9 minutes. From that perspective, 1 or 2 minutes doesn't
sound slow at all...

Some tips nonetheless:
- '6/20/2005 23:59:59.59' is not the last possible timestamp on the 20th
(because that would be '6/20/2005 23:59:59.997'). It would be better to
rewrite the predicate as: vcdStartDt >= '20050620' and vcdStartDt <
'20050621'

- You could add an index that covers the query, for example on
tblVoIPTempCallDetails(vcdStartDt, vdsName, vdsDescription,
vcdBillingSec). Of course, the downside of this is, that this extra
index will impact Insert performance

- You could change index IX_tblVoIPTempCallDetails from nonclustered to
clustered. This way, you are sure that SQL-Server will not have to do a
table scan if you select just one day. Also, it would help if you also
added a nonclustered index on tblVoIPTempCallDetails(vdsName,
vdsDescription), or even on tblVoIPTempCallDetails(vdsName,
vdsDescription, vcdBillingSec) to make it a covering index (combined
with the clustered index on vcdStartDt).

- You omitted the GROUP BY clause. Make sure you do not write:
group by vdsName + isnull(' - '+vdsDescription,'')
but use the following notation instead:
GROUP BY vdsName, vdsDescription

HTH,
Gert-Jan


Hisham wrote:

Yup, here is the table description

//------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[tblVoIPTempCallDetails] (
[vcdId] [int] NOT NULL ,
[acctid] [int] NULL ,
[userId] [int] NULL ,
[userFN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[userLN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[userUnixName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[custId] [int] NULL ,
[Organization] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[callCost] [numeric](18, 0) NULL ,
[callRate] [smallmoney] NULL ,
[callDiscount] [smallmoney] NULL ,
[callAdjRate] [smallmoney] NULL ,
[vdsId] [int] NULL ,
[vdsDialCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vdsName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vdsDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[vcsId] [int] NULL ,
[vcsName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcsColor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcdStartDt] [datetime] NULL ,
[vcdBillingSec] [int] NULL ,
[vcdAccountcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[vcdSrcNum] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcdSrcName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vcdSrcChannel] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[vcdDstPrefix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcdDstNum] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vcdDstChannel] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[callGK] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vrName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vrDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[vrUserId] [int] NULL ,
[vtcMonth] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vtcDay] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vtcActive] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblVoIPTempCallDetails] WITH NOCHECK ADD
CONSTRAINT [PK_tblVoIPTempCallDetails] PRIMARY KEY CLUSTERED
(
[vcdId]
) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblVoIPTempCallDetails] ADD
CONSTRAINT [DF_tblVoIPTempCallDetails_callCost] DEFAULT (0) FOR
[callCost],
CONSTRAINT [DF_tblVoIPTempCallDetails_callRate] DEFAULT (0) FOR
[callRate],
CONSTRAINT [DF_tblVoIPTempCallDetails_callDiscount] DEFAULT (0) FOR
[callDiscount],
CONSTRAINT [DF_tblVoIPTempCallDetails_callAdjRate] DEFAULT (0) FOR
[callAdjRate],
CONSTRAINT [DF_tblVoIPTempCallDetails_vtcActive] DEFAULT (1) FOR
[vtcActive]
GO

CREATE INDEX [IX_tblVoIPTempCallDetails] ON
[dbo].[tblVoIPTempCallDetails]([vcdStartDt] DESC ) WITH FILLFACTOR =
70 ON [PRIMARY]
GO

CREATE INDEX [IX_tblVoIPTempCallDetails_1] ON
[dbo].[tblVoIPTempCallDetails]([acctid]) WITH FILLFACTOR = 70 ON
[PRIMARY]
GO
//------------------------------------------------------------------------------------------------------

and the query I use is the following:

select
vdsName + isnull(' - '+vdsDescription,'') callSummaryName,
min(vcdStartDt) callMinDt,
max(vcdStartDt) callMaxDt,
count(*) callTotalCount,
isnull(sum(vcdBillingSec),0) callTotalSec,
from tblVoIPTempCalldetails where vcdStartDt between '6/20/2005
0:0:00.01' and '6/20/2005 23:59:59.59'

thanx for all your help!

[Erland Sommarskog]

Gert-Jan Strik (so***@toomuchspamalready.nl) writes:

c) There is no covering index for your query. This basically means that
in this case, SQL-Server will have to scan the entire table, except for
the cached pages. Let's assume that 1.5 GB of your internal memory
already holds data of the tblVoIPTempCallDetails. This still leaves
585,000 pages to be read.
Actually, the index on vcdStartDt would only require 250000 reads. And,
assuming that vcdStartDt is correlated with vcdId, those 250000 reads
would yield a high cache-hit ratio. So...
If all the assumptions above are correct (which is highly unlikely), and
SQL-Server would really perform a clustered index scan, then the query
would take some 9 minutes. From that perspective, 1 or 2 minutes doesn't
sound slow at all...
There you have the answer why the query is as fast as it is.

As for the rest of the analysis, there is not much to add to Gert-Jan's
analysis when it comes to the performance part. By adding good indexes,
you can make enormous performance gains. I would go for the covered index:
- You could add an index that covers the query, for example on
tblVoIPTempCallDetails(vcdStartDt, vdsName, vdsDescription,
vcdBillingSec). Of course, the downside of this is, that this extra
index will impact Insert performance

I would also like to give some remark on the table design. Except for
the primary key, all columns are nullable. Does this really mirror
business rules? Can vcdStartDt really be NULL? Not that this affects
performance any in significant way, but when I see this table design,
I get the impression that this was created by someone left-handedly.
And data-modelling for a database this size is definitely a job for
both hands.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp