huge table,every month new 21million row data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

huge table,every month new 21million row data

Author

Topic

yba
Starting Member

5 Posts

Posted - 2009-10-19 : 08:56:43

Hi,

Our most important table has 40 column (1 datetime, 1 device id int, allothers int)
we have one clustered index from datetime and device id column
we have total 500 device,
every hour approximately 30.000 new data is inserting from our devices,
so every month 21.600.000 new rows,

every milisecond sp's, query's run,
in %99,9 of these query's last one hours data is queried,
in a day ony 4-5 times old datas are queried,
but all queries we use indexs -(exp: last half hour, device 23)

Every month we split our table, so every month is in a different table, there ise a index table, so if i need a specific times data, first i look index table, i learn which table i must query,

3 year ago we think this is the best scenario for us, becouse after one month if we do not split our main table, our database becomes to slow,

We have itainum cluster server and 32gb ram,
total we have 10 database
what is the best scenario for us you think :)
we use indexing but, sql 2005 uses all memory :)
for example i will make a montly analysis, complex query takes 15minutes, but if i restart sql service before query, it takes 1-2 minutes, so no free memory is a problem, but i dont know why it uses all memory, microsoft says that "its not important dont look memory usage, if sql needs memory its free up" but its a lie i think

Sory about my bad english

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-19 : 10:00:41

Ok, you have 32GB in the server.
What other applications are running on the server?
Do you have many linked server/CLR/extended procedures?
What have you set the SQL Server min and max memory to?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-19 : 10:40:26

Seems like a case of parameter sniffing and/or bad execution plans.
Can you post your 15-minute query?

N 56°04'39.26"
E 12°55'05.63"

yba
Starting Member

5 Posts

Posted - 2009-10-19 : 11:14:40

Thanks,
Server only runs Sql and its services,

Mostly our web server .net c# aplications makes queries,
A lot of aplications wrotten by different companies, developers in or company

When we look, yes the queries are very bad, they mostly use sellect queries, now we are revising all old codes for optimization, we are changing queries to sp, before these, when i restart sql service, in one hour it was taking the all ram, but know, it useges 15gb of ram. My question is, old datas not used in memory is free-up automatical by Sql server, so why when i have less than 2-3gb free memory my queries takes too long (for example if i query arciheve data, old months data, all free ram finishes, but after this query, i never use old data, but it never free up memory), if i restart service, they run too fast, so i restart sql service in a period, this is a true behavior?

yes our queries are not optimized, becouse our programmers didnt need it, i think they said o very powerfull server, to much ram, lets use it

Now we are trying to optimiese all our aplication, sp, queries..

min and max memories are in default vaules,

quote:
Originally posted by YellowBug

Ok, you have 32GB in the server.
What other applications are running on the server?
Do you have many linked server/CLR/extended procedures?
What have you set the SQL Server min and max memory to?

yba
Starting Member

5 Posts

Posted - 2009-10-19 : 11:29:48

quote:
Originally posted by Peso

Seems like a case of parameter sniffing and/or bad execution plans.
Can you post your 15-minute query?

N 56°04'39.26"
E 12°55'05.63"

OK,
but this query can be optimezed but we use it once a month only, my question why not sql free up memory automaticly, and spliting table is a good idea or not for huges tables? And when i query all table, once a month, with below query, my sql dies :) why when this query finishes it doesnt free up memory

This query, used in montly data, (more then 21million data)
devices send data random,
for example,
data time - value
00:00:12 - 73
00:00:55 - 742
00:01:22 - 41
00:02:01 - 74
00:02:56 - 33
00:03:45 - 5
..
..
00:10:00- 6

this query makes this
00:00-00:10 avg data is:253 sum:2893
00:10-00:20 avg data is:145 sum:5956
..

DECLARE @start_time datetime
DECLARE @end_time datetime
DECLARE @max_time datetime

SET @start_time='2009-03-01 00:00:00.000'
SET @end_time=DATEADD(minute,10,@start_time)
SET @max_time='2009-04-01 00:00:00.000'

PRINT @max_time

WHILE (@end_time<@max_time)
BEGIN
INSERT INTO xtable (START_TIME,END_TIME,DeviceId,XPL1_TOP,XPL2_TOP,XPL3_TOP,XPL4_TOP,XPL5_TOP,XPL6_TOP,XPL7_TOP,XPL8_TOP,D1_TOP,D2_TOP,D3_TOP,D4_TOP,D5_TOP,D6_TOP,D7_TOP,D8_TOP, R1,R2,R3,R4,R5,R6,R7,R8,G1,G2,G3,G4,G5,G6,G7,G8,GHG,GGG,MsgID)
(SELECT @start_time AS START,@end_time AS ENDT, DeviceID AS RTMS, SUM(XPL1) AS XPL1_TOP,SUM(XPL2) AS XPL2_TOP, SUM(XPL3) AS XPL3_TOP, SUM(XPL4) AS XPL4_TOP, SUM(XPL5)
AS XPL5_TOP, SUM(XPL6) AS XPL6_TOP, SUM(XPL7) AS XPL7_TOP, SUM(XPL8) AS XPL8_TOP,
SUM(D1) AS D1_TOP,SUM(D2) AS D2_TOP, SUM(D3) AS D3_TOP, SUM(D4) AS D4_TOP, SUM(D5)
AS D5_TOP, SUM(D6) AS D6_TOP, SUM(D7) AS D7_TOP, SUM(D8) AS D8_TOP, AVG(R1) AS R1,
AVG(R2) as y2, AVG(R3) AS y3, AVG(R4) as R4,AVG(R5) AS R5, AVG(R6) as R6, AVG(R7) AS R7,
AVG(R8) as R8, AVG(G1) AS G1, AVG(G2) as G2, AVG(G3) AS G3, AVG(G4) as G4,AVG(G5) AS G5, AVG(G6) as G6,
AVG(G7) AS G7, AVG(G8) as G8,AVG(GHG) as Oort, AVG(GGG) as Sort, COUNT(MsgID) as MsgID
FROM dbo.monthTable where (MsgTime>=@start_time and MsgTime< @end_time) GROUP BY DeviceID)
SET @start_time=@end_time
SET @start_time=DATEADD(ms,1,@start_time)
SET @end_time=DATEADD(minute,10,@start_time)
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-19 : 16:56:18

[code]DECLARE @start_time datetime,
@max_time datetime

SELECT @start_time = '20090301',
@max_time = '20090401'

INSERT xtable
(
START_TIME,
END_TIME,
DeviceId,
XPL1_TOP,
XPL2_TOP,
XPL3_TOP,
XPL4_TOP,
XPL5_TOP,
XPL6_TOP,
XPL7_TOP,
XPL8_TOP,
D1_TOP,
D2_TOP,
D3_TOP,
D4_TOP,
D5_TOP,
D6_TOP,
D7_TOP,
D8_TOP,
R1,
R2,
R3,
R4,
R5,
R6,
R7,
R8,
G1,
G2,
G3,
G4,
G5,
G6,
G7,
G8,
GHG,
GGG,
MsgID
)
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, msgTime) / 10 * 10, 0) AS START,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, msgTime) / 10 * 10, '18991231 23:59:59') AS ENDT,
DeviceID AS RTMS,
SUM(XPL1) AS XPL1_TOP,
SUM(XPL2) AS XPL2_TOP,
SUM(XPL3) AS XPL3_TOP,
SUM(XPL4) AS XPL4_TOP,
SUM(XPL5) AS XPL5_TOP,
SUM(XPL6) AS XPL6_TOP,
SUM(XPL7) AS XPL7_TOP,
SUM(XPL8) AS XPL8_TOP,
SUM(D1) AS D1_TOP,
SUM(D2) AS D2_TOP,
SUM(D3) AS D3_TOP,
SUM(D4) AS D4_TOP,
SUM(D5) AS D5_TOP,
SUM(D6) AS D6_TOP,
SUM(D7) AS D7_TOP,
SUM(D8) AS D8_TOP,
AVG(R1) AS R1,
AVG(R2) as y2,
AVG(R3) AS y3,
AVG(R4) as R4,
AVG(R5) AS R5,
AVG(R6) as R6,
AVG(R7) AS R7,
AVG(R8) as R8,
AVG(G1) AS G1,
AVG(G2) as G2,
AVG(G3) AS G3,
AVG(G4) as G4,
AVG(G5) AS G5,
AVG(G6) as G6,
AVG(G7) AS G7,
AVG(G8) as G8,
AVG(GHG) as Oort,
AVG(GGG) as Sort,
COUNT(MsgID) as MsgID
FROM dbo.monthTable
where MsgTime >= @start_time
AND MsgTime < @max_time
GROUP BY DeviceID,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, msgTime) / 10 * 10, 0)[/code]

N 56°04'39.26"
E 12°55'05.63"

yba
Starting Member

5 Posts

Posted - 2009-10-20 : 03:55:28

quote:
Originally posted by Peso

[code]DECLARE @start_time datetime,
@max_time datetime

SELECT @start_time = '20090301',
@max_time = '20090401'

....
..
.

Yes, I tested,

Your Optimized Query,
(3.172.845 row(s) affected)
00:03:22

Our Query,
(3.172.587 row(s) affected)
00:06:40

this ss is taken after restart sql service and run your query,

this ss is taken after restart sql service and run our query,

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-20 : 04:18:07

quote:
min and max memories are in default vaules

I'd suggest you set the SQL Server memory to (or similiar):
min: 4GB
max: 24GB - 30GB
There are several articles on the subject online. At least, look into setting the SQL Server max memory limit so the OS always has sufficient memory available.

BTW, The number of rows affected by the two queries above are different. Or is that a typo?

yba
Starting Member

5 Posts

Posted - 2009-10-20 : 08:58:47

quote:
Originally posted by YellowBug

quote:
min and max memories are in default vaules

I'd suggest you set the SQL Server memory to (or similiar):
min: 4GB
max: 24GB - 30GB
There are several articles on the subject online. At least, look into setting the SQL Server max memory limit so the OS always has sufficient memory available.

BTW, The number of rows affected by the two queries above are different. Or is that a typo?

Thanks, I performed your suggestion values
Yes row counts diffent, they are becouse of zero seconds and miliseconds I think,
one table
2009-01-01 00:00:00.000 2008-12-31 23:59:59.000
other table
2009-01-01 00:00:00.000 2009-01-01 00:10:00.000
difference is becouse of this.

Everyone says, "ohh dont restart your server, its funy"
but example, after these queries my server become slow, if i restart it becomes fast

so why not

by time to time, my memory usage increases, and after value server becomes turtle

and other question, yes we are using indexs
but spliting tables increases performance, this is a best practice or not

thanks all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources