Feeds:
Posts
Comments

Archive for April, 2013

ColumnStore Index & Forceseek table hints enhancements are two new features introduced in SQL Server 2012. The error message we will discuss today is related to these two enhancements because they both cannot be used together.

Let’s discuss this in detail:

Message Number: 366

Severity : 16

Error Message: The query processor could not produce a query plan because the FORCESEEK hint on table or view ‘%.*ls’ cannot be used with the column store index ‘%.*ls’.

Error Generation:
Let me create a sample to demonstrate this error.

--First Create a columnstore index on [Purchasing].[PurchaseOrderDetail]
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_PurchaseOrderDetail_ColumnStore]
ON [Purchasing].[PurchaseOrderDetail]
(
[ReceivedQty],
[ProductID]
)
WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

--Try to use it with FORCESEEK table hint

Select * from [Purchasing].[PurchaseOrderDetail]
WITH (FORCESEEK,INDEX (IX_PurchaseOrderDetail_ColumnStore))
--OUTPUT

Msg 366, Level 16, State 1, Line 1
The query processor could not produce a query plan because the FORCESEEK hint on table or view ‘PurchaseOrderDetail’ cannot be used with the column store index ‘IX_PurchaseOrderDetail_ColumnStore’.

Ooopps…… I am unable to execute it.

Resolution:

The reason behind this error is that we cannot use COLUMNSTORE INDEX with FORCESEEK table hint because columnstore index already boosted the performance of the query on that particular table. So, we do not use any further FORCESEEK table hints on that table. But sometime we create columnstore index on some other fields and we need to do forceseek on some other fields, so in that case we can create another non cluster index and point it to forceseek table hint.

Give below are the script.

--Create a new noncluster index BUT NOT COLUMNSTORE INDEX
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_Sample] ON [Purchasing].[PurchaseOrderDetail]
(
[ReceivedQty],
[ProductID]
)
WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

--Execute the query against the index
Select * from [Purchasing].[PurchaseOrderDetail]
WITH (FORCESEEK,INDEX (IX_PurchaseOrderDetail_Sample))
Where ProductID>2 And ReceivedQty>5
--OUTPUT

error366.1.1

Conclusion :
Remember, whenever you FORCESEEK with index, make sure that the index is not a columnstore index. However, if you need to use FORCESEEK table hint with index on the same table, you can create another index without columnstore and use it in FORCESEEK.

Clean Database :
Execute the given below script to drop the sample indexes.

DROP INDEX [IX_PurchaseOrderDetail_ColumnStore] 
ON [Purchasing].[PurchaseOrderDetail]
DROP INDEX [IX_PurchaseOrderDetail_Sample] 
ON [Purchasing].[PurchaseOrderDetail]

Read Full Post »

sys.dm_os_memory_cache_counters is one of the dynmaic management views shipped with SQL Server 2005. The major benefits of this dynamic management view is to provide the list of summary stating how cache is allocated against each request.

Given below is a simple query that will give the details about cache.

--This query is compatibale with SQL Server 2005 and 2008 only.
Select [name],[type],SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

dm_os_memory_cache_counters1.1

A few days ago I tried using the same query in SQL Server 2012 to check out the summary of cache but the above query did not work. Given below is the error detail.

Select [name],[type]
,SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb] 
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘single_pages_kb’.
Msg 207, Level 16, State 1, Line 3
Invalid column name ‘multi_pages_kb’.
sys.dm_os_memory_cache_counter1.2

Oooops……………
It generated an error. The first impression I got was that these two fields have been eliminated from sys.dm_os_memory_cache_counters in SQL Server 2012 ?

But the answer is No, these field are not eliminated but renamed. Given below are the details.


S.No


Previous Column Name


New Column Name

1

single_pages_kb

pages_kb

2

multi_pages_kb

pages_in_use_kb

Lets rename the column in the above query and execute it again in SQL Server 2012.

--This query is compatible with SQL Server 2012 and above.
Select [name],[type]
,SUM([pages_kb]) As [pages_kb]
,SUM([pages_in_use_kb]) As [pages_in_use_kb]
from sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

sys.dm_os_memory_cache_counter1.3

Conclusion :
In SQL Server 2012, sys.dm_os_memory_cache_counters has renamed the two columns single_pages_kb, multi_pages_kb to pages_kb, pages_in_use_kb respectively. Remember to implement this renamed column change effects to avoid such errors.

Reference : MSDN

Read Full Post »

Sometimes, you feel very handicapped when you need to convert some complex data types but due to compatibility, the built-in conversion functions such as Cast, Convert, Try_Cast, Try_Convert cannot aid conversion.

How to convert float to varchar is indeed an old topic. In this article, we will solve this issue via STR function and FORMAT function introduced in SQL Server 2012.

Given below are the two methods to convert float to varchar.

Method 1:
In this method, we will use STR function to convert float to varchar and thereafter LTRIM to trim the extra spaces left in it.

--This script is compatible with SQL Server 2005 and above.
Declare @varchar as varchar(50)
Declare @float as float

Set @float =1234567.12345678
Select @float as [Float]

Set @varchar =LTRIM(STR(@float,50,8))
Select @varchar as [Float converted to varchar]
--OUTPUT

Convertfloortovarchar1.1

Method 2:
In the above method, you can notice that we used two different methods (STR, LTRIM) to convert float to varchar and to trim extra spaces. In this method we will use ONLY FORMAT function to convert float to varchar.

--This script is compatible with SQL Server 2012 and above.
Declare @varchar as varchar(50)
Declare @float as float

Set @float =1234567.12345678
Select @float as [Float]

Set @varchar =Format(@float,'#.#############')
Select @varchar as [Float converted to varchar]
--OUTPUT

convertfloattovarchar1.2

Read Full Post »

“How to find the SQL Server port number ?” has been an old topic being discussed in multiple forums. The common solution provided is using xp_instance_regread. It is one of the useful undocumented stored procedures when you need to read registry settings. In this article, I will give you two solutions. First one using xp_instance_regread and second, using sys.dm_server_registry, a new dynamic management view shipped in SQL Server 2008 R2

Method 1 : Using xp_instance_regread.
In this method we will use xp_instance_regread to find the port number.

--This script will work on SQL Server 2005 and above
DECLARE @HkeyLocal nvarchar(18)
Declare @Instance varchar(100)
DECLARE @MSSqlServerRegPath nvarchar(200)
DECLARE @PortNumber nvarchar(100)

SET @Instance ='MSSQL11.MSSQLSERVER'
SET @HkeyLocal=N'HKEY_LOCAL_MACHINE'
SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\\Microsoft SQL Server\'
+ @Instance + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

Print @MSSqlServerRegPath
EXEC xp_instance_regread @HkeyLocal
, @MSSqlServerRegPath
, N'TcpPort'
, @PortNumber OUTPUT
SELECT @PortNumber as [Port Number]
--OUTPUT

Port Number
————
1433

(1 row(s) affected)

Method 2 : Using using sys.dm_server_registry.
In this method we will use sys.dm_server_registry to find the port number.

--This script will work on SQL Server 2012 and above
Use master
GO
Select value_data as [Port Number]
From sys.dm_server_registry
Where registry_key like '%IPALL%' and
value_name ='TcpPort'

Port Number
————
1433

(1 row(s) affected)

Conclusion :
If you compare both methods, you will feel the difference, because the code is much optimized in the second method. Also, you can utilize the result set of second method further without using any temporary table but on the other hand you need temporary table to utilize the result set further.

Read Full Post »

How to get the day of the week ? I have been asked this question several times and we do have its solution via DATENAME function. However today we will achieve the same solution using FORMAT function, introduced in SQL Server 2012 as well. Given below are the two methods that help in identifying the day of a week in SQL Server 2005 & SQL Server 2012 respectively.
Method 1 :
Script supported in SQL Server 2005 and above :

 --This script will work in SQL Server 2005 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select DATENAME(dw , @Datetime) as [Weekday Name]
--OR
Select DATENAME(weekday, @Datetime) as [Weekday Name]
--OUTPUT

Get week day name1.1

Method 2 :
Script supported in SQL Server 2012 and above :

 --This script will work in SQL Server 2012 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select FORMAT(@Datetime,'dddd') as [Weekday Name]
--OUTPUT

Get week day name1.2

Have you come across any other method ? Do share.

Read Full Post »

SQL Server new versions always come up with the new performance optimization techniques, enhancements and additions. FORCESEEK table hint enhancement is also one of the new enhancements that came up in SQL Server 2012.

Let me create an index on Purchasing. [PurchaseOrderDetail] table to demonstrate this enhancement.

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_PurchaseOrderID]
ON Purchasing.[PurchaseOrderDetail]
(
[PurchaseOrderID] ASC
) WITH (DROP_EXISTING = OFF) ON [PRIMARY]

FORCESEEK in earier version of SQL :
In the ealier version of SQL Server, FORCESEEK table hint needed only an index on the table and it could optimize your query. But sometimes you may have more than one index and you cannot define those indexes anywhere in the syntax of forceseek (Limitation). Let me script it to elaborate the same.

---This query will work on SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with (forceseek)
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

FORCESEEK in SQL Server 2012 :
In SQL Server 2012, you can define index name along with the column in forceseek table hint to further optimize your query. But you can also use the above syntax  and it will not break your code.

Let me explain it with examples :

Example 1 : (Define the index name in forceseek)

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with
(forceseek,INDEX(IX_PurchaseOrderDetail_PurchaseOrderID))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Example 2 : (Define the index name along with the column name in forceseek)

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d
with (forceseek(IX_PurchaseOrderDetail_PurchaseOrderID(PurchaseOrderID)))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Conclusion:

In SQL Server 2012, FORCESEEK  came up with the very nice enhancement that you can define index name along with the column in FORCESEEK, to have further control on query optimization. However, remember this is the last option we should choose at the time of optimization. Finally, this is only recommended for experienced programmers.

Reference : MSDN

Read Full Post »

« Newer Posts - Older Posts »