question

Fatherjack avatar image
Fatherjack asked

Master Database scans

I have a 2008 instance that is showing a lot of batch requests/sec and it would appear (via profiler) that the Master database is having 00's of scans/sec. I have never seen anything like it, there are no other servers here with this occurring. Not really sure where to go next. There is next to no information in profiler - no textdata, no sqlhandle ... Only trace flad 1204 and 1222 are running and turning them off makes no difference. Any ideas please?
sql-server-2008profilerscan-event
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Scans on what tables/indexes? There are no strange filters set in profiler that are hiding something are there? Related issue?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

profiler doesnt show that. No details from Master that anything is being scanned. SO post seems to allude to misdirection from Profiler - the data actually relates to a different db ....
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Have you looked at sys.dm_exec_requests? You should be able to see stuff going by there. Then link over to sys.dm_exec_sqltext. If it's encrypted you'll still get a row, although you won't see the query.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

not yet. have been using SQL Monitor, Profiler, sys.dm_os_performance_counters, Ignite (Free) and almost all of my swear words.
0 Likes 0 ·
OK - that gets me a query running inside SCCM which is hosted on this server. Guessing there isnt a hashtag of #sccmhelp !
0 Likes 0 ·
it worked? i was kind of hoping you'd get to use the rest of your swear words.
0 Likes 0 ·
Oh, there's plenty of cursing to be done. No idea how SCCM works so whether that's the issue or not is going to be a voyage of discovery
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
I would be tempted to set up an extended event session that captured some information about anything being executed on master. CREATE EVENT SESSION [TraceMaster] ON SERVER ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.nt_username, sqlserver.username) WHERE sqlserver.database_id = 1), ADD EVENT sqlserver.module_end (ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.username) WHERE sqlserver.database_id = 1) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON) If you use [SQLSarg's extended events manager][1] (note I said SQLSarg because I don't want to mis-spell his surname!) then it makes it pretty easy to get the output out - plus then you can add actions for more info (for example, sqlserver.sql_text to get the input buffer)... Hahah - it took me so long to get the event session SQL right, that there are two better answers now! [1]: http://extendedeventmanager.codeplex.com/
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Found it! Under the View menu, I was looking in Tools. Oddly I only found a picture of me in that menu.
2 Likes 2 ·
I almost rolled up my sleeves to work with XE but bailed as its Friday. Will take this into battle on Monday.
0 Likes 0 ·
right, I installed the manager a while back but cant find it to launch it. Where does it begin?
0 Likes 0 ·
Hahah most certainly not sir! We all have our bad days. :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.