April 4, 2011 at 8:46 pm
When you start up SQL Server, if you were to run:
SELECT * from tempdb.sys.tables
there are a handful of table variables in the results. Does anyone have an idea of what they are used for, and what processes are using them?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 4, 2011 at 10:10 pm
WayneS (4/4/2011)
When you start up SQL Server, if you were to run:
SELECT * from tempdb.sys.tablesthere are a handful of table variables in the results. Does anyone have an idea of what they are used for, and what processes are using them?
What identifies them as table variables?
I assume they are part of the CHECKDB process that runs when SQL Server starts.
I can see what I think is information related to them in sys.dm_tran_active_transactions and sys.dm_tran_database_transactions, but there is nothing in sys.dm_tran_session_transactions.
On my test server there were 5 created during the start, and then 8 more created about 7 minutes later. (I checked before I restarted SQL Server and there were none, so they go away eventually.)
Of course there are no open transactions listed in sys.dm_exec_requests, so I assume they are some really background processes.
April 4, 2011 at 11:42 pm
UMG Developer (4/4/2011)
I assume they are part of the CHECKDB process that runs when SQL Server starts.
CheckDB doesn't run when SQL starts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2011 at 11:44 pm
WayneS (4/4/2011)
When you start up SQL Server, if you were to run:
SELECT * from tempdb.sys.tablesthere are a handful of table variables in the results. Does anyone have an idea of what they are used for, and what processes are using them?
Got object explorer open? SSMS's code tends to the messy side, could easily be table variables or temp tables that SQL caches within the code it runs.
Go have a look at their columns, may give you a hint as to what they are used for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2011 at 8:36 am
Well, I tracked this down to a procedure running through a job (I forgot to disable/delete it after finished with testing it), and in the process I learned something I didn't know before about local temp tables.
This procedure is creating 2 local temporary tables - but what is showing up in tempdb.sys.tables for the table name is not the temporary table name as specified in the procedure - instead, it looks more like the table variable naming scheme, ie. #0DAF0CB0.
I knew that for performance reasons, SQL does something that allows the last 50 used temp tables to be "reclaimed" by another process without having to go through the stuff to build the object, reducing contention in tempdb. Is this what is going on here?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 5, 2011 at 9:44 am
GilaMonster (4/4/2011)
UMG Developer (4/4/2011)
I assume they are part of the CHECKDB process that runs when SQL Server starts.CheckDB doesn't run when SQL starts.
Gila,
Then what are all of these log entries that are created during startup:
2011-04-04 20:54:04.50 spid26s Starting up database 'ReportServerTempDB'.
2011-04-04 20:54:04.50 spid25s Starting up database 'ReportServer'.
2011-04-04 20:54:04.50 spid31s Starting up database 'AdventureWorksDW'.
2011-04-04 20:54:04.50 spid28s Starting up database 'AdventureWorksDW2008'.
2011-04-04 20:54:04.50 spid30s Starting up database 'AdventureWorks'.
2011-04-04 20:54:04.50 spid24s Starting up database 'msdb'.
2011-04-04 20:54:04.50 spid29s Starting up database 'AdventureWorksLT2008'.
2011-04-04 20:54:04.50 spid32s Starting up database 'AdventureWorksLT'.
2011-04-04 20:54:04.50 spid33s Starting up database 'AdventureWorks2008R2'.
2011-04-04 20:54:04.50 spid27s Starting up database 'AdventureWorks2008'.
2011-04-04 20:54:04.51 spid34s Starting up database 'AdventureWorksDW2008R2'.
2011-04-04 20:54:04.51 spid35s Starting up database 'AdventureWorksLT2008R2'.
2011-04-04 20:54:04.58 spid27s CHECKDB for database 'AdventureWorks2008' finished without errors on 2011-03-23 20:23:13.340 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.60 spid30s CHECKDB for database 'AdventureWorks' finished without errors on 2011-03-23 20:23:19.727 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.61 spid37s CHECKDB for database 'AdminDB' finished without errors on 2011-03-23 20:23:29.857 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.61 spid30s Recovery is writing a checkpoint in database 'AdventureWorks2008' (7). This is an informational message only. No user action is required.
2011-04-04 20:54:04.64 spid35s CHECKDB for database 'AdventureWorksLT2008R2' finished without errors on 2011-03-23 20:23:29.183 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.66 spid33s CHECKDB for database 'AdventureWorks2008R2' finished without errors on 2011-03-23 20:23:23.690 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.67 spid28s CHECKDB for database 'AdventureWorksDW2008' finished without errors on 2011-03-23 20:23:18.450 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.68 spid29s CHECKDB for database 'AdventureWorksLT2008' finished without errors on 2011-03-23 20:23:19.390 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.71 spid34s CHECKDB for database 'AdventureWorksDW2008R2' finished without errors on 2011-03-23 20:23:28.203 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.72 spid32s CHECKDB for database 'AdventureWorksLT' finished without errors on 2011-03-23 20:23:23.393 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.72 spid31s CHECKDB for database 'AdventureWorksDW' finished without errors on 2011-03-23 20:23:22.483 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.83 spid24s CHECKDB for database 'msdb' finished without errors on 2011-03-23 20:23:11.620 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.89 spid26s CHECKDB for database 'ReportServerTempDB' finished without errors on 2011-03-23 20:23:13.110 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:04.93 spid25s CHECKDB for database 'ReportServer' finished without errors on 2011-03-23 20:23:12.757 (local time). This is an informational message only; no user action is required.
2011-04-04 20:54:05.21 spid7s Recovery is complete. This is an informational message only. No user action is required.
I didn't say it was a full CHECKDB, but as far as I can tell some portion of CHECKDB is run when SQL Server starts.
April 5, 2011 at 10:58 am
UMG Developer (4/5/2011)
GilaMonster (4/4/2011)
UMG Developer (4/4/2011)
I assume they are part of the CHECKDB process that runs when SQL Server starts.CheckDB doesn't run when SQL starts.
Gila,
Then what are all of these log entries that are created during startup:
Look at the time of the log entry. Look at the time the message gives.
2011-04-04 20:54:04.58 spid27s CHECKDB for database 'AdventureWorks2008' finished without errors on 2011-03-23 20:23:13.340 (local time). This is an informational message only; no user action is required.
I didn't say it was a full CHECKDB, but as far as I can tell some portion of CHECKDB is run when SQL Server starts.
Nope. SQL will never run a checkDB automatically, not portion, not part, not some. CheckDB runs when you schedule it or manually run it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2011 at 11:00 am
WayneS (4/5/2011)
I knew that for performance reasons, SQL does something that allows the last 50 used temp tables to be "reclaimed" by another process without having to go through the stuff to build the object, reducing contention in tempdb. Is this what is going on here?
Yup. Not necessarily last 50, there are restrictions on which ones it can cache. When it caches one, the name is stripped and it's given a name that looks like a table var.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2011 at 11:03 am
GilaMonster (4/5/2011)
Look at the time of the log entry. Look at the time the message gives.
Guess I should read the whole message. 😉 I was assuming that it ran a mini-CHECKDB as part of recovery when it started to verify the state of the database. Thanks for pointing that out.
April 5, 2011 at 11:22 am
UMG Developer (4/5/2011)
GilaMonster (4/5/2011)
Look at the time of the log entry. Look at the time the message gives.Guess I should read the whole message. 😉 I was assuming that it ran a mini-CHECKDB as part of recovery when it started to verify the state of the database. Thanks for pointing that out.
What the message is giving you is the last time that checkDB ran successfully (without finding any corruptions) for each database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply