October 27, 2023 at 4:32 pm
Hi
After some help . I've got a bunch of wait resources from sysprocesses that don't include specific resource type i.e its not prefixed with KEY, or TAB etc
This type of format 66:1:6666666
So I presume the first numbers are the databaseid, maybe the second number is file id , but I cant decode the 3rd number , doesnt appear to be objectid.
Any words of wisdom ?
October 27, 2023 at 4:58 pm
you should not be using that sysprocesses anymore and should instead be using one of the system views/functions. see https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql?view=sql-server-2017
the format can vary depending on resourcetype - one of the examples would be dbid:fileid:pageid
do have a look at this example using newer views.
https://blog.sqlauthority.com/2015/11/25/sql-server-what-resource-wait-are-we-seeing/
October 27, 2023 at 5:02 pm
agreed 100% , but this is something Ive inherited and the data capture was from sysprocesses and thats what Ive got to work with in this instance (-:
So as I mentioned it doesn't specify resource type , I tried the format you mentioned and it just errors
Table error: DBCC PAGE page (1:8791710) (object ID 0, index ID 0, partition ID 0, alloc unit ID 0 (type Unknown)) is out of the range of this database.
October 28, 2023 at 8:33 pm
You might find this script useful which can help decipher the wait resource:
https://github.com/trimble-oss/dba-dash/blob/main/DBADashGUI/SQL/DecipherWaitResource.sql
It's from the open-source monitoring tool I created - DBA Dash.
DBA Dash - Free, open source monitoring for SQL Server
October 28, 2023 at 10:31 pm
sys.dm_exec_requests and sys.dm_os_waiting_tasks dynamic management view provide more detailed information about the wait types and wait resources, making it easier to diagnose performance issues.
October 29, 2023 at 4:48 pm
Hi David, many thanks alas the tool returns the same error I received when attempting to manually decode
Table error: DBCC PAGE page (1:8630847) (object ID 0, index ID 0, partition ID 0, alloc unit ID 0 (type Unknown)) is out of the range of this database.
Looking at stack overflow I see this
"This pattern means that DBCC couldn’t work out which object the page is part of."
However having ran a dbcc checkdb I see no errors etc ,, Im off to go bang my head against a wall
October 29, 2023 at 4:50 pm
can you post the FULL entry of sysprocesses - all columns
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply