sql7sp4-server (winnt4sp6a) to much paged
-
Can anyone help me with this one.
I'm trying to determine why this sql7sp4-server (winnt4sp6a) is eating op more than 900Mb VM (560MbMemusage).
This gradualy grows, but I cannot determine why. There are no open transactions.
(Maybe cut and paste this back in notepad)
** syscacheobjects -- prepared objects **
**-------------------------------------**
bucketid cacheobjtype objtype objid dbid uid refcounts usecounts pagesused setopts langid dateformat status sqlbytes sql
----------- ----------------- -------- ----------- ------ ------ ----------- ----------- ----------- ----------- ------ ---------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
1 Parse Tree SysTab 1 1 1 1 1 15 185 0 1 0 20 sysobjects
2 Parse Tree SysTab 2 1 1 1 1 2 153 0 1 0 20 sysindexes
3 Parse Tree SysTab 1 3 1 1 1 15 185 0 1 0 20 sysobjects
4 Parse Tree SysTab 2 2 1 1 1 2 153 0 1 0 20 sysindexes
4 Parse Tree SysTab 1 4 1 1 1 15 185 0 1 0 20 sysobjects
5 Parse Tree SysTab 1 5 1 1 2 15 153 0 1 0 20 sysobjects
6 Parse Tree SysTab 1 6 1 1 1 15 185 0 1 0 20 sysobjects
6 Parse Tree SysTab 2 3 1 1 1 2 153 0 1 0 20 sysindexes
7 Parse Tree SysTab 1 7 1 1 1 15 185 0 1 0 20 sysobjects
8 Parse Tree SysTab 2 4 1 1 1 1 153 0 1 0 20 sysindexes
8 Parse Tree SysTab 1 8 1 1 1 15 185 0 1 0 20 sysobjects
10 Parse Tree SysTab 2 5 1 1 1 2 153 0 1 0 20 sysindexes
12 Parse Tree SysTab 2 6 1 1 1 2 153 0 1 0 20 sysindexes
14 Parse Tree SysTab 2 7 1 1 1 2 153 0 1 0 20 sysindexes
16 Parse Tree SysTab 2 8 1 1 1 2 153 0 1 0 20 sysindexes
30 Parse Tree SysTab 30 1 1 1 3 1 185 0 1 0 24 sysdatabases
65 Executable Plan Prepared 952268373 5 -2 1 2 1 249 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 varchar(30),@4 int,@5 varchar(30),@6 int,@7 int,@8 varchar(30))INSERT [gi].[v_MatDBLog]([smelt
65 Compiled Plan Prepared 952268373 5 -2 2 2 2 249 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 varchar(30),@4 int,@5 varchar(30),@6 int,@7 int,@8 varchar(30))INSERT [gi].[v_MatDBLog]([smelt
79 Compiled Plan Adhoc 143318448 2 -2 1 1 4 185 0 1 0 256 select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (case when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then
158 Compiled Plan Adhoc 143318448 4 -2 1 1 4 185 0 1 0 256 select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (case when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then
189 Parse Tree View 245575913 4 1 1 20 5 185 0 1 0 24 sysjobs_view
1118 Parse Tree View 974626515 5 5 1 2 2 249 0 1 0 26 v_recept_kalk
1810 Parse Tree View 1026102696 5 5 1 6 3 249 0 1 0 20 v_MatDBLog
2350 Executable Plan Proc 1494296383 1 1 1 1 1 185 0 1 0 34 sp_MSdbuseraccess
2350 Compiled Plan Proc 1494296383 1 1 2 1 7 185 0 1 0 34 sp_MSdbuseraccess
2860 Compiled Plan Prepared 208562073 5 -2 1 1 1 249 0 1 1 106 Select bunker_nr, artikel_nr from gi.v_BunkerVoorkeur
2993 Compiled Plan Prepared 982200715 5 -2 2 2 2 249 0 1 0 132 SELECT * FROM [master].[dbo].[syscacheobjects] ORDER BY [bucketid]
3060 Executable Plan Prepared 359259591 5 -2 1 1 1 761 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 varchar(30),@4 varchar(30),@5 varchar(30),@6 varchar(30))INSERT [gi].[v_EventLog_OVEN1]([tseve
3060 Compiled Plan Prepared 359259591 5 -2 2 2 2 761 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 varchar(30),@4 varchar(30),@5 varchar(30),@6 varchar(30))INSERT [gi].[v_EventLog_OVEN1]([tseve
3130 Compiled Plan Prepared 373195443 5 -2 1 1 1 249 0 1 1 256 Select bunker_nr, artikel_nr, max(gewicht) as gewicht from gi.v_MatDBLog where smeltnr = '403028' and lijn_prod = 'OVEN1' and fl
3321 Extended Proc Proc 517576882 1 1 1 8 1 249 0 1 0 26 sp_cursoropen
4153 Executable Plan Proc 1563152614 4 1 1 2 1 185 0 1 0 50 sp_get_composite_job_info
4153 Compiled Plan Proc 1563152614 4 1 2 1 29 185 0 1 0 50 sp_get_composite_job_info
5176 Parse Tree Default 2085582468 5 5 1 1 1 761 0 1 0 46 DF_t_alice_temp_ts_crea
5200 Executable Plan Prepared 308537031 5 -2 1 69 3 249 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 varchar(30),@4 varchar(30),@5 varchar(30),@6 varchar(30),@7 varchar(30),@8 varchar(30),@9 varc
5200 Compiled Plan Prepared 308537031 5 -2 2 2 14 249 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 varchar(30),@4 varchar(30),@5 varchar(30),@6 varchar(30),@7 varchar(30),@8 varchar(30),@9 varc
5596 Parse Tree View 962102468 5 5 1 2 2 761 0 1 0 32 v_AlarmLog_OVEN1
6471 Executable Plan Prepared 916443298 5 -2 1 1 1 249 0 1 0 132 SELECT [name]=[name] FROM [master].[dbo].[sysdatabases] ORDER BY 1
6471 Compiled Plan Prepared 916443298 5 -2 2 2 1 249 0 1 0 132 SELECT [name]=[name] FROM [master].[dbo].[sysdatabases] ORDER BY 1
6578 Parse Tree Default 258099960 5 5 1 1 1 761 0 1 0 56 DF_t_eventlog_alice_fl_saved
7369 Compiled Plan Prepared 892882390 5 -2 1 2 1 249 0 1 0 256 (@1 varchar(30))SELECT [smeltnr]=[smeltnr],[artikel_nr]=[artikel_nr],[gewicht]=[gewicht],[proces_stap]=[proces_stap] FROM [gi].[
7369 Compiled Plan Prepared 892882390 5 -2 1 2 1 761 0 1 0 256 (@1 varchar(30))SELECT [smeltnr]=[smeltnr],[artikel_nr]=[artikel_nr],[gewicht]=[gewicht],[proces_stap]=[proces_stap] FROM [gi].[
7369 Compiled Plan Prepared 892882390 5 -2 1 2 1 249 0 1 1 256 (@1 varchar(30))SELECT [smeltnr]=[smeltnr],[artikel_nr]=[artikel_nr],[gewicht]=[gewicht],[proces_stap]=[proces_stap] FROM [gi].[
9199 Parse Tree Default 1470628282 5 5 1 1 1 249 0 1 0 46 DF_t_ReceptLog_receptId
10984 Executable Plan Adhoc 803918409 1 -2 1 1 1 185 0 1 0 196 select loginname from dbo.sysusers u, master.dbo.syslogins l where u.name = user and u.sid = l.sid
10984 Compiled Plan Adhoc 803918409 1 -2 2 1 1 185 0 1 0 196 select loginname from dbo.sysusers u, master.dbo.syslogins l where u.name = user and u.sid = l.sid
12083 Compiled Plan Adhoc 140035949 5 -2 1 1 1 761 0 1 0 256 Insert into gi.v_EventLog_OVEN1 (tseventbegin,tagname,tagdescr,value,priority,status) values('01/13/2004 11:15:11','BEV_SMELT_OV
12946 Executable Plan Proc 2075154438 1 1 1 1 2 185 0 1 0 46 sp_helppullsubscription
12946 Compiled Plan Proc 2075154438 1 1 2 1 3 185 0 1 0 46 sp_helppullsubscription
12985 Parse Tree Default 1534628510 5 5 1 1 1 249 0 1 0 44 DF_t_ReceptLog_fl_sync
13463 Executable Plan Proc 2094630505 1 1 1 1 1 185 0 1 0 42 sp_MSSQLDMO70_version
13463 Compiled Plan Proc 2094630505 1 1 2 1 1 185 0 1 0 42 sp_MSSQLDMO70_version
14186 Parse Tree Default 1618104805 5 5 1 1 1 249 0 1 0 44 DF_t_MatDBLog_receptId
15150 Parse Tree View 1790629422 5 5 1 2 2 249 0 1 0 32 v_BunkerVoorkeur
16079 Parse Tree Default 1650104919 5 5 1 1 1 249 0 1 0 46 DF_t_MatDBLog_fl_update
16494 Extended Proc Proc 501576825 1 1 1 30 1 249 0 1 0 28 sp_cursorfetch
16944 Executable Plan Prepared 916443298 6 -2 1 1 1 249 0 1 0 132 SELECT [name]=[name] FROM [master].[dbo].[sysdatabases] ORDER BY 1
16944 Compiled Plan Prepared 916443298 6 -2 2 2 1 249 0 1 0 132 SELECT [name]=[name] FROM [master].[dbo].[sysdatabases] ORDER BY 1
17929 Executable Plan Proc 1461580245 4 1 1 2 10 185 0 1 0 22 sp_help_job
17929 Compiled Plan Proc 1461580245 4 1 2 1 5 185 0 1 0 22 sp_help_job
17972 Parse Tree Default 1682105033 5 5 1 1 1 249 0 1 0 42 DF_t_MatDBLog_ts_krea
20704 Executable Plan Prepared 14061160 5 -2 1 2 1 249 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 int,@4 int,@5 varchar(30),@6 int,@7 int,@8 int,@9 varchar(30),@10 varchar(30),@11 varchar(30))
20704 Compiled Plan Prepared 14061160 5 -2 2 2 2 249 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 int,@4 int,@5 varchar(30),@6 int,@7 int,@8 int,@9 varchar(30),@10 varchar(30),@11 varchar(30))
20758 Executable Plan Prepared 96585568 5 -2 1 4 1 761 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 varchar(100),@4 varchar(30),@5 varchar(30),@6 varchar(30))INSERT [gi].[v_EventLog_OVEN1]([tsev
20758 Compiled Plan Prepared 96585568 5 -2 2 2 2 761 0 1 0 256 (@1 varchar(30),@2 varchar(30),@3 varchar(100),@4 varchar(30),@5 varchar(30),@6 varchar(30))INSERT [gi].[v_EventLog_OVEN1]([tsev
21035 Extended Proc Proc 1454628225 1 1 1 2 1 185 0 1 0 42 xp_sqlagent_enum_jobs
21758 Compiled Plan Adhoc 143318448 3 -2 1 1 4 185 0 1 0 256 select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (case when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then
22358 Executable Plan Prepared 770812353 5 -2 1 36 1 249 0 1 0 256 (@1 int,@2 int,@3 varchar(30),@4 varchar(30),@5 tinyint)UPDATE [gi].[v_Mat_Bunkers_OVEN1] SET [artikel_nr]=@1,[gewicht]=@2,[ts_w
22358 Compiled Plan Prepared 770812353 5 -2 2 2 2 249 0 1 0 256 (@1 int,@2 int,@3 varchar(30),@4 varchar(30),@5 tinyint)UPDATE [gi].[v_Mat_Bunkers_OVEN1] SET [artikel_nr]=@1,[gewicht]=@2,[ts_w
22450 Parse Tree View 1694629080 5 5 1 2 2 249 0 1 0 38 v_Mat_Bunkers_OVEN1
22503 Parse Tree View 1042102753 5 5 1 4 4 249 0 1 0 22 v_ReceptLog
23485 Parse Tree Default 338100245 5 5 1 2 1 761 0 1 0 56 DF_t_EventLog_OVEN1_fl_Saved
23976 Compiled Plan Adhoc 984713924 8 -2 1 1 1 185 0 1 0 144 select ceiling((sum(size) * (8192.0/1024.0)) / 1024.0) from dbo.sysfiles
24094 Executable Plan Prepared 136108618 5 -2 1 57 1 761 0 1 0 256 (@1 varchar(100),@2 varchar(30),@3 int,@4 varchar(30),@5 varchar(30),@6 varchar(30),@7 int,@8 int,@9 int)INSERT [gi].[t_EventLog
24094 Compiled Plan Prepared 136108618 5 -2 2 2 2 761 0 1 0 256 (@1 varchar(100),@2 varchar(30),@3 int,@4 varchar(30),@5 varchar(30),@6 varchar(30),@7 int,@8 int,@9 int)INSERT [gi].[t_EventLog
24396 Parse Tree View 1010102639 5 5 1 4 2 761 0 1 0 32 v_EventLog_OVEN1
24769 Executable Plan Proc 1870629707 1 1 1 18 3 185 0 1 0 30 sp_MStablespace
24769 Compiled Plan Proc 1870629707 1 1 2 1 5 185 0 1 0 30 sp_MStablespace
24769 Executable Plan Proc 1870629707 1 1 1 21 3 185 0 1 0 30 sp_MStablespace
24769 Compiled Plan Proc 1870629707 1 1 2 1 5 185 0 1 0 30 sp_MStablespace
24769 Executable Plan Proc 1870629707 1 1 1 35 3 185 0 1 0 30 sp_MStablespace
24769 Compiled Plan Proc 1870629707 1 1 2 1 5 185 0 1 0 30 sp_MStablespace
24769 Executable Plan Proc 1870629707 1 1 1 119 3 185 0 1 0 30 sp_MStablespace
24769 Compiled Plan Proc 1870629707 1 1 2 1 5 185 0 1 0 30 sp_MStablespace
24769 Executable Plan Proc 1870629707 1 1 1 18 3 185 0 1 0 30 sp_MStablespace
24769 Compiled Plan Proc 1870629707 1 1 2 1 5 185 0 1 0 30 sp_MStablespace
24769 Executable Plan Proc 1870629707 1 1 1 60 3 185 0 1 0 30 sp_MStablespace
24769 Compiled Plan Proc 1870629707 1 1 2 1 5 185 0 1 0 30 sp_MStablespace
24769 Executable Plan Proc 1870629707 1 1 1 19 3 185 0 1 0 30 sp_MStablespace
24769 Compiled Plan Proc 1870629707 1 1 2 1 5 185 0 1 0 30 sp_MStablespace
24769 Executable Plan Proc 1870629707 1 1 1 18 3 185 0 1 0 30 sp_MStablespace
24769 Compiled Plan Proc 1870629707 1 1 2 1 5 185 0 1 0 30 sp_MStablespace
25378 Parse Tree Default 306100131 5 5 1 2 1 761 0 1 0 48 DF_t_EventLog_OVEN1_guid
25505 Parse Tree View 697769543 1 1 1 2 7 185 0 1 0 18 syslogins
26140 Compiled Plan Adhoc 492467036 5 -2 1 1 1 761 0 1 0 256 Insert into gi.t_EventLog_Alice_OVEN1 (guid_alice,timestamp,hseconde,device,infobits,datatype,byte,bitnr,status) values('{1FA10E
26662 Compiled Plan Adhoc 356385948 1 -2 1 1 1 185 0 1 0 84 exec sp_MStablespace N'[dbo].[sysxlogins]'
27087 Executable Plan Proc 949578421 4 1 1 1 1 185 0 1 0 32 sp_help_category
27087 Compiled Plan Proc 949578421 4 1 2 1 2 185 0 1 0 32 sp_help_category
27271 Parse Tree Default 274100017 5 5 1 1 1 761 0 1 0 66 DF_t_EventLog_Alice_OVEN1_ts_krea
27417 Executable Plan Prepared 916443298 7 -2 1 1 1 249 0 1 0 132 SELECT [name]=[name] FROM [master].[dbo].[sysdatabases] ORDER BY 1
27417 Compiled Plan Prepared 916443298 7 -2 2 2 1 249 0 1 0 132 SELECT [name]=[name] FROM [master].[dbo].[sysdatabases] ORDER BY 1
27523 Executable Plan Prepared 138134761 5 -2 1 1 1 761 0 1 0 172 (@1 varchar(3000),@2 int)INSERT [gi].[t_alice_temp]([datastring],[stat]) VALUES(@1,@2)
27523 Compiled Plan Prepared 138134761 5 -2 2 2 1 761 0 1 0 172 (@1 varchar(3000),@2 int)INSERT [gi].[t_alice_temp]([datastring],[stat]) VALUES(@1,@2)
27842 Compiled Plan Prepared 646565903 5 -2 1 1 1 249 0 1 1 158 SELECT bunker_nr, artikel_nr, gewicht FROM gi.v_recept_kalk ORDER BY artikel_nr
28672 Compiled Plan Adhoc 97396955 6 -2 1 1 1 249 0 1 0 108 select name from master.dbo.sysdatabases order by name
29213 Executable Plan Proc 1481772336 1 1 1 1 2 185 0 1 0 56 sp_helpmergepullsubscription
29213 Compiled Plan Proc 1481772336 1 1 2 1 6 185 0 1 0 56 sp_helpmergepullsubscription
29655 Parse Tree Default 2037582297 5 5 1 1 1 761 0 1 0 40 DF_t_alice_temp_guid
29667 Extended Proc Proc 485576768 1 1 1 4 1 249 0 1 0 28 sp_cursorclose
30116 Compiled Plan Adhoc 984713924 1 -2 1 1 1 185 0 1 0 144 select ceiling((sum(size) * (8192.0/1024.0)) / 1024.0) from dbo.sysfiles
31560 Extended Proc Proc 325576198 1 1 1 1 1 185 0 1 0 16 xp_msver
32376 Compiled Plan Prepared 697383198 5 -2 1 2 1 249 0 1 1 256 Select bunker_nr, artikel_nr, gewicht, deel_recept_nr, volg_nr, recepttype from gi.v_ReceptLog where HeatNr = 'ml000021891' and
32714 Executable Plan Proc 885578193 4 1 1 1 1 185 0 1 0 36 sp_verify_category
32714 Compiled Plan Proc 885578193 4 1 2 1 2 185 0 1 0 36 sp_verify_category
32986 Parse Tree Default 1570104634 5 5 1 1 1 249 0 1 0 60 DF_t_SignaalLog_OVEN1_fl_saved
33634 Compiled Plan Adhoc 491522055 5 -2 1 1 1 761 0 1 0 256 Insert into gi.t_EventLog_Alice_OVEN1 (guid_alice,timestamp,hseconde,device,infobits,datatype,byte,bitnr,status) values('{1FA10E
33678 Parse Tree Default 1518628453 5 5 1 1 1 249 0 1 0 48 DF_t_ReceptLog_fl_update
35571 Parse Tree Default 1550628567 5 5 1 1 1 249 0 1 0 44 DF_t_ReceptLog_ts_krea
36535 Parse Tree View 1858105660 5 5 1 2 2 761 0 1 0 46 v_ladingsmodel_recepten
36535 Parse Tree View 1858105660 5 5 1 6 2 249 0 1 0 46 v_ladingsmodel_recepten
37288 Executable Plan Prepared 435050885 5 -2 1 2 1 761 0 1 0 166 (@P1 uniqueidentifier)DELETE FROM "Steelplant"."gi"."t_alice_temp" WHERE "guid"=@P1
37288 Compiled Plan Prepared 435050885 5 -2 2 1 1 761 0 1 0 166 (@P1 uniqueidentifier)DELETE FROM "Steelplant"."gi"."t_alice_temp" WHERE "guid"=@P1
37425 Executable Plan Proc 1835153583 1 1 1 1 1 153 0 1 0 48 sp_MSreplcheck_subscribe
37425 Compiled Plan Proc 1835153583 1 1 2 1 1 153 0 1 0 48 sp_MSreplcheck_subscribe
37935 Executable Plan Prepared 394885183 5 -2 1 1 1 761 0 1 0 256 (@P1 datetime,@P2 uniqueidentifier)UPDATE "Steelplant"."gi"."t_AlarmLog_OVEN1" SET "tsalarmend"=@P1 WHERE "guid"=@P2 AND "tsalar
37935 Compiled Plan Prepared 394885183 5 -2 2 1 1 761 0 1 0 256 (@P1 datetime,@P2 uniqueidentifier)UPDATE "Steelplant"."gi"."t_AlarmLog_OVEN1" SET "tsalarmend"=@P1 WHERE "guid"=@P2 AND "tsalar
38665 Parse Tree Default 1666104976 5 5 1 1 1 249 0 1 0 42 DF_t_MatDBLog_fl_sync
39212 Compiled Plan Prepared 36033282 5 -2 1 1 1 249 0 1 1 256 SELECT smeltnr, artikel_nr, gewicht, proces_stap FROM gi.v_ladingsmodel_recepten WHERE heatnr = 'ml000021891'ORDER BY artikel_nr
39950 Compiled Plan Adhoc 511853266 5 -2 1 1 3 249 0 1 0 256 INSERT INTO gi.v_SignaalLog_OVEN1 (ts_registratie, d001,d002,d003,d004,d005,d006,d007,d008,d009,d010,d011,d012,d013,d014,d015,d0
40320 Compiled Plan Adhoc 696659104 1 -2 1 1 1 185 0 1 0 256 select spid, status, N'loginname' = suser_sname(sid), hostname, program_name, cmd, N'dbname' = db_name(dbid), cpu, memusage, blo
40528 Executable Plan Prepared 522906607 5 -2 1 1 1 761 0 1 0 230 (@1 varchar(30),@2 varchar(30))SELECT * FROM [gi].[v_AlarmLog_OVEN1] WHERE (([tsalarmbegin]=@1) AND ([tagname]=@2))
40528 Compiled Plan Prepared 522906607 5 -2 2 2 1 761 0 1 0 230 (@1 varchar(30),@2 varchar(30))SELECT * FROM [gi].[v_AlarmLog_OVEN1] WHERE (([tsalarmbegin]=@1) AND ([tagname]=@2))
40764 Compiled Plan Prepared 178189834 4 -2 1 2 1 185 0 1 0 256 (@1 tinyint)SELECT [category_id]=[category_id],[category_type]=[category_type],[name]=[name] FROM [msdb].[dbo].[syscategories] W
40947 Extended Proc Proc 629577281 1 1 1 3 1 761 0 1 0 26 sp_executesql
41303 Parse Tree View 1090102924 5 5 1 2 32 249 0 1 0 36 v_SignaalLog_OVEN1
41315 Executable Plan Proc 43147199 1 1 1 1 2 185 0 1 0 30 sp_MSdbuserpriv
41315 Compiled Plan Proc 43147199 1 1 2 1 21 185 0 1 0 30 sp_MSdbuserpriv
41802 Compiled Plan Prepared 260884335 1 -2 1 2 2 185 0 1 0 256 (@1 int)SELECT [spid]=[spid],[status]=[status],[loginname]=suser_sname([sid]),[hostname]=[hostname],[program_name]=[program_name
42104 Compiled Plan Adhoc 984713924 3 -2 1 1 1 185 0 1 0 144 select ceiling((sum(size) * (8192.0/1024.0)) / 1024.0) from dbo.sysfiles
(139 row(s) affected)
** DBCC SQLMGRSTATS -- Algemeen **
**------------------------------**
Item Status
------------------------- -----------
Memory Used (8k Pages) 81
Number CSql Objects 31
Number False Hits 0
(3 row(s) affected)
** DBCC memorystatus --Undocumented **
**----------------------------------**
Buffer Distribution Value
------------------------------ -----------
Stolen 231
Free 20785
Procedures 458
Inram 0
Dirty 908
Kept 17
I/O 0
Latched 0
Other 89665
(9 row(s) affected)
Buffer Counts Value
------------------------------ -----------
Commited 112064
Target 129840
Hashed 90590
InternalReservation 41
ExternalReservation 0
Min Free 40
(5 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 139
TotalPages 458
InUsePages 151
(3 row(s) affected)
Dynamic Memory Manager Value
------------------------------ -----------
Stolen 689
OS 346
General 714
QueryPlan 283
Optimizer 0
Utilities 8
Connection 30
(7 row(s) affected)
Global Memory Objects Value
------------------------------ -----------
Resource 365
Locks 32
XDES 1
SQLCache 81
Replication 2
LockBytes 2
ServerGlobal 20
(7 row(s) affected)
Query Memory Manager Value
------------------------------ -----------
Grants 0
Waiting 0
Available 97091
Maximum 97091
(4 row(s) affected)
Optimization Queue Value
------------------------------ -----------
Optimizing 0
Waiting 0
Available 4
Maximum 4
(4 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** DBCC proccache -- enkel procedurecache **
**-----------------------------------------**
num proc buffs num proc buffs used num proc buffs active proc cache size proc cache used proc cache active
-------------- ------------------- --------------------- --------------- --------------- -----------------
283 283 -36 458 458 139
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** DBCC CACHESTATS **
**-----------------**
Object Type Hit Ratio Object Count Avg Cost Avg Pages LW Object Count LW Avg Cost LW Avg Stay (ms) LW Avg Use Count
----------- ----------------------------------------------------- ------------ ----------------------------------------------------- ----------------------------------------------------- --------------- ----------------------------------------------------- ----------------------------------------------------- -----------------------------------------------------
Proc 0.80373831775700932 42 0.97619047619047616 4.0238095238095237 0 0.0 0.0 0.0
Prepared 0.98947368421052628 39 0.97435897435897434 1.5897435897435896 0 0.0 0.0 0.0
Adhoc 0.0 15 1.0 1.7333333333333334 0 0.0 0.0 0.0
ReplProc 0.0 0 0.0 0.0 0 0.0 0.0 0.0
Trigger 0.0 0 0.0 0.0 0 0.0 0.0 0.0
View 0.63636363636363635 12 1.0 5.416666666666667 0 0.0 0.0 0.0
Default 0.0625 15 1.0 1.0 0 0.0 0.0 0.0
UsrTab 0.0 0 0.0 0.0 0 0.0 0.0 0.0
SysTab 0.14999999999999999 16 1.0 7.5625 0 0.0 0.0 0.0
Check 0.0 0 0.0 0.0 0 0.0 0.0 0.0
Rule 0.0 0 0.0 0.0 0 0.0 0.0 0.0
Summary 0.75292587776332898 139 0.98561151079136688 3.2949640287769784 0 0.0 0.0 0.0
(12 row(s) affected)
** DBCC MEMUSAGE (default niet uitgevoerd) **
**-----------------------------------------**
** DBCC SQLPERF (LOGSPACE) **
**-------------------------**
Database Name Log Size (MB) Log Space Used (%) Status
-------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ -----------
SteelplantMES 284.99219 0.38566709 0
Steelplant 511.99219 30.948254 0
msdb 3.4921875 65.659958 0
tempdb 0.7421875 73.125771 0
model 0.7421875 50.394737 0
master 1.2421875 60.455975 0
(8 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** DBCC SQLPERF (UMSSTATS) **
**-------------------------**
Statistic Value
-------------------------------- ------------------------
Scheduler ID 0.0
num users 12.0
num runnable 0.0
num workers 16.0
idle workers 9.0
work queued 0.0
cntxt switches 5.1072212E+7
cntxt switches(idle) 1.6846498E+7
work processed 4610490.0
(9 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** DBCC SQLPERF (WAITSTATS) **
**--------------------------**
Wait Type Requests Wait Time Signal Wait Time
-------------------------------- ------------------------ ------------------------ ------------------------
MISCELLANEOUS 1.3147745E+7 3.9603946E+8 2.0127363E+8
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 0.0 0.0 0.0
LCK_M_IS 97.0 430901.0 0.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_IS_S 0.0 0.0 0.0
LCK_M_IX 31.0 670.0 15.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_S 24.0 26017.0 16.0
LCK_M_U 7.0 1892.0 0.0
LCK_M_II_NL 0.0 0.0 0.0
LCK_M_II_X 0.0 0.0 0.0
LCK_M_IU 0.0 0.0 0.0
LCK_M_IS_U 0.0 0.0 0.0
LCK_M_X 37.0 299.0 0.0
LCK_M_BU 0.0 0.0 0.0
RESOURCE_SEMAPHORE 0.0 0.0 0.0
DTC 0.0 0.0 0.0
OLEDB 0.0 0.0 0.0
WRITELOG 2643889.0 5688933.0 513257.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 0.0 0.0 0.0
XCB 1844.0 67917.0 48.0
DBTABLE 0.0 0.0 0.0
EC 0.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 0.0 0.0 0.0
CXPACKET 0.0 0.0 0.0
PAGESUPP 0.0 0.0 0.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 0.0 0.0 0.0
CURSOR 0.0 0.0 0.0
LATCH_EX 0.0 0.0 0.0
LATCH_SH 0.0 0.0 0.0
LATCH_UP 0.0 0.0 0.0
PAGELATCH_EX 211720.0 78403.0 76483.0
PAGELATCH_SH 1024.0 15.0 15.0
PAGELATCH_UP 23.0 32.0 0.0
PAGEIOLATCH_EX 2327.0 2071.0 77.0
PAGEIOLATCH_SH 3496.0 10558.0 315.0
PAGEIOLATCH_UP 11164.0 23841.0 752.0
NETWORKIO 0.0 0.0 0.0
Total 1.6023428E+7 4.0237098E+8 2.0186462E+8
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** DBCC SQLPERF (IOSTATS) **
**------------------------**
Statistic Value
-------------------------------- ------------------------
Reads Outstanding 0.0
Writes Outstanding 0.0
(2 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** DBCC SQLPERF (RASTATS) **
**------------------------**
Statistic Value
-------------------------------- ------------------------
RA Pages Found in Cache 0.0
RA Pages Placed in Cache 0.0
RA Physical IO 0.0
Used Slots 0.0
(4 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** DBCC SQLPERF (THREADS) **
**------------------------**
Spid Thread ID Status LoginName IO CPU MemUsage
----- ---------- ---------- ---------- ----------- ----------- -----------
1 sleeping NULL 0 0 44
2 background NULL 0 0 -5
3 background NULL 0 0 0
4 sleeping NULL 0 0 0
5 sleeping NULL 5489 0 0
6 background NULL 323 0 0
7 10961(0x2a runnable ALZDM1\B07 6 0 -21159
8 10918(0x2a sleeping ALZDM1\B07 0 0 0
9 10918(0x2a sleeping gi 8 0 20
10 10918(0x2a sleeping gi 552 0 297
11 10918(0x2a runnable gi 0 0 0
12 10872(0x2a sleeping ALZDM1\B07 8 0 -1
13 10947(0x2a sleeping ALZDM1\B07 8 0 11
(13 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** DBCC PROCCACHE **
**----------------**
num proc buffs num proc buffs used num proc buffs active proc cache size proc cache used proc cache active
-------------- ------------------- --------------------- --------------- --------------- -----------------
283 283 -36 458 458 139
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** sp_configure **
**----------------**
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 0
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
This was removed by the editor as SPAM
-
I am not sure that this is all of your problem but the cntxt switches looks high for the amount of work processed. In order for your processor to take that kind of context switching, depending on the processor you have, it may be digging into your memory.
cntxt switches 5.1072212E+7
cntxt switches(idle) 1.6846498E+7
work processed 4610490.0
Check the recompiles of your sp's.
Hope this helps you get to the answer.
-
Thanks for the reply, but the thread is over 1 year old
The sp-recompiles is what we've worked on, as well as ram-extention.
We've also detected that many mem is consumed because of scans for bit-columns as filter-criteria.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply