Why Temp table Name has maximum of 116 character length, whereas other has 128??? and SP_Server_Info

 

EXEC SP_SERVER_INFO is a procedure which gives server related information from your SQL Server; it returns a list of attribute names and matching values for attributes.

attribute_id attribute_name attribute_value

1 DBMS_NAME Microsoft SQL Server

2 DBMS_VER Microsoft SQL Server 2012 - 11.0.3000.0

10 OWNER_TERM owner

11 TABLE_TERM table

12 MAX_OWNER_NAME_LENGTH 128

13 TABLE_LENGTH 128

14 MAX_QUAL_LENGTH 128

15 COLUMN_LENGTH 128

16 IDENTIFIER_CASE MIXED

17 TX_ISOLATION 2

18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52

19 SAVEPOINT_SUPPORT Y

20 MULTI_RESULT_SETS Y

22 ACCESSIBLE_TABLES Y

100 USERID_LENGTH 128

Here we can see the TABLE_NAME attribute is 128, which means you can have table name 128 character long, though, a regular tablename can be 128 character long, a temporary table cannot be longer than 116 character

Why temporary table name has maximum character length of 116, whereas others have 128, let’s find out the technical reason behind it.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i INT)

CREATE TABLE #B(i INT)

SELECT LEN(name) 'Length', name

FROM tempdb.sys.objects

WHERE TYPE = 'u'

AND name LIKE '#AAAAAA%'

OR name LIKE '#B%'

Whatever object name you give for temp table, SQL is going to pad the value with underscores and few “magic” bit as the end and would cover complete 128 characters. Have a closer look at length of magic bit “000000000058” and “000000000059” in above output [yeah, its 12]. Those are needed because you are allowed to create same name temp table by different sessions.  The length of magical number is 12 so 128 – 12 = 116 is the max length which is allowed for temp objects as SQL adds 12 chars at the end.

Be in this world with your code; make sure your code speak loud!

 

Post Reference: Vikram Aristocratic Elfin Share

Fantastic DMV in SQL Server 2014, sys.dm_exec_query_profiles

You can check the progress of query execution using this DMV, you can get information of each operator used in Actual Query Plan in detail through this dmv.

Lets play around with dm_exec_query_profiles.

Test 1

I opened a SSMS and run my query in session 1

-- My Long query in session 1

select * from sys.all_columns

CROSS JOIN sys.objects

GO

At the same time I opened another query window(another session, session 2) and run my dm_exec_query_profiles

select * from sys.dm_exec_query_profiles

(0 row(s) affected)

What I found is zero record though a heavy query is running in session1 but it didn’t capture the query progress.

Test 2

I found that to get the result from this DMV you need to trigger this. And to trigger this DMV you need to SET STATISTICS PROFILE ON

Again in session1

SET STATISTICS PROFILE ON

-- My Long query in session 1

select * from sys.all_columns

CROSS JOIN sys.objects

GO

Session 2

select * from sys.dm_exec_query_profiles

select session_id,physical_operator_name,node_id,row_count,

estimate_row_count,first_row_time,last_active_time,cpu_time_ms

from sys.dm_exec_query_profiles

So here lot of interesting information we captured through this DMV, all the information and progress of running query including the time taken by each physical operator, row count etc.

But only point of disappointment is it need either actual Query plan on or Set profiler on the running query for which we want to capture the progress of query.

My code is my fire. Fire and individual cannot sleep, when I will get prospect of sleep!!!

 

Post Reference: Vikram Aristocratic Elfin Share

Cardinality Estimation - IV CE -Running your Query under Legacy Cardinality Estimator Compatibility Level i.e Under Compatibility Level 110 in SQL Server 2014 with CL 120

Let see how we will be retrieving the legacy SQL Server 2008 compatibility level. First lets turn your SQL Server 2014 to 120 Compatibility level

Changing the COMPATIBLITY LEVEL OF SQL Server 2014 to 120i.e. the highest compatibility level of SQL Family.

alter database AdventureWorks2012

set COMPATIBILITY_LEVEL = 120

go

Command(s) completed successfully.

Lets fire the query in both the environment one under  SQL Server 2014 compatibility level 120 and other on SQL Server 2012 Compatibility level 120 by enabling TRACE Flag to 9481

use AdventureWorks2012

go

-- With New Cardinality Estimator 2014

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

-- With Legacy Cardinality Estimator 2012

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

OPTION (QUERYTRACEON 9481);

Lets see both the query plan to the check the Estimated Row Count.

Query with Compatibility Level of 120 (SQL Server 2014)

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Now here you can see, in first legacy plan the Estimated No of Rows has 1 count whereas in the second New Cardinality Estimator Plan the Estimated No of Rows has 3 counts to 2312.

Your code is warm, filled up sparks; bring into play to alleviate the world of business!!!

 

Post Reference: Vikram Aristocratic Elfin Share

Cardinality Estimator Series III- Enabling New Cardinality Estimator (SQL Server 2014) with TRACE FLAG 2312, when your DB Compatibility level set to Legacy Server i.e. SQL Server 2012,2008..

Lets first set the database SQL SERVER 2014 to the legacy compatibility level of 110 i.e. SQL Server 2008 and with TRACE FLAG we will see the new Query Plan.

Changing the COMPATIBLITY LEVEL OF SQL Server 2014 to 110 i.e. SQL Server 2012

use master

go

alter database AdventureWorks2012

set COMPATIBILITY_LEVEL = 110

go

Command(s) completed successfully.

Lets fire the query in both the environment one under  SQL Server 2012 compatibility level 110 and other on SQL Server 2014 Compatibility level 120 by enabling TRACE Flag to 2132

use AdventureWorks2012

go

-- With Legacy Cardinality Estimator 2012

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

-- With New Cardinality Estimator 2014

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

OPTION (QUERYTRACEON 2312);

Lets see both the query plan to the check the Estimated Row Count.

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Now here you can see, in first legacy plan the Estimated No of Rows has 1 count whereas in the second New Cardinality Estimator Plan the Estimated No of Rows has 3 counts to 2312.

Blaze up your code and people will come from miles to see your code burning!

 


Post Reference: Vikram Aristocratic Elfin Share