Archive

Archive for October, 2015

SQL Tips – Check count of records in Temporary Tables from other sessions

October 13, 2015 Leave a comment

 
Recently one Stored Procedure was giving issues on our Production system. The SP had some temporary (#) tables that were getting populated in sequence, but the final result was not as expected. As Temp-Tables cannot be accessed across other sessions you cannot see what’s going on. Also as its a PROD system I could not dissect or change the SP and see what’s happening inside.

Debugging a SP on a DEV machine is simple. I can add some extra checks after temp-table population code to get the record count by using “SELECT @@rowcount”, and was able to see the issues.

But this cannot be done on PROD, as you cannot alter the SPs there. So, to see which temp table is being populated you can use below query. This will also show the records count if any temp table is in mid of population.

SELECT 
	T.NAME AS TABLE_NAME,
	S.ROW_COUNT
FROM TEMPDB.sys.dm_db_partition_stats AS S 
INNER JOIN TEMPDB.sys.tables AS T 
ON S.OBJECT_ID = T.OBJECT_ID 
WHERE S.INDEX_ID < 2
and T.NAME like '%#TempTable%'; -- Give #Table name here

temptable-reccount


Categories: SQL Tips Tags:

SQL Error – The database principal owns a schema in the database, and cannot be dropped

October 10, 2015 1 comment

Have you ever faced this error while dropping a USER from a Database? Today while dropping a USER I faced this error and for a while I was not able to fix it. After checking here & there and searching online I found some clues and got the reason for this error.

User_Drop01

–> Let’s check the reason behind it and how to fix it:

USE [AdventureWorks2014]
GO

DROP USER [hruser]
GO

You get following error:

Msg 15138, Level 16, State 1, Line 5
The database principal owns a schema in the database, and cannot be dropped.

The above error explains very well why the USER cannot be deleted. It’s because the USER is associated with a SCHEMA and has its ownership. Thus until and unless you assign another USER as the SCHEMA owner you cannot DROP this USER.
 

–> To check which SCHEMA this USER owns you can use either of these 2 methods:

Method #1: By simple Query:

SELECT *
FROM sys.schemas
WHERE principal_id = USER_ID ('hruser')
GO
name	schema_id	principal_id
Sales	9		5

 

Method #2: By GUI in SSMS: Right click on the user name under Database -> Security -> Users

User_Drop02

–> So, all you need to do is change the ownership of the Schema from the USER that you want to delete to some other user like dbo, as done below:.

USE [AdventureWorks2014]
GO

ALTER AUTHORIZATION ON SCHEMA::[Sales] TO [dbo]
GO

If there are more SCHEMAs then issue ALTER AUTHORIZATION ON SCHEMA statement on those schemas also to change their owners.
 

–> Ok, now just re-run the DROP USER statement, it will drop the User.

USE [AdventureWorks2014]
GO

DROP USER [hruser]
GO

SQL Server 2016 CTP 2.4 update is here | download now

October 1, 2015 Leave a comment

As pert of Microsoft’s rapid release model for SQL Server, Microsoft today announced the CTP 2.4 update of the recently and initially released Community Technology Preview (CTP) 2.2 version of SQL Server 2016 and the CTP 2.3, link.
 

[Register and Download the CTP 2.4 Evaluation version (180 days) here]
 

–> Direct download link (~2.3 GB):

– Box file SQLServer2016-x64-ENU.box
– EXE file SQLServer2016-x64-ENU.exe

 

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2016 (CTP2.4) – 13.0.600.65 (X64) Sep 20 2015 01:45:59 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 10240: )
 

–> Enhancements and Issues fixed in this release:

1. Operational Analytics and In-Memory Data Warehouse improvements

– Existing Non-Clustered ColumnStore Indexes (NCCI) are updateable without requiring index rebuild.

– Parallel Index build of Non-Clustered ColumnStore Index (NCCI), leveraging available cores/logical processors available on the server for significant performance improvement.

– INSERT operations performance in delta Row Group. (Prior to CTP2.4, the delta rowgroup data was PAGE compressed, which added overhead for Insert operations. With CTP2.4, the data in delta rowgroup is no longer PAGE compressed.)

– Better Query performance with Non-Clustered ColumnStore Index (NCCI), with:
a. String predicate pushdown.
b. Improved query path when processing deleted rows by bypassing delete buffer when possible.

2. SQL Server Smart Maintenance Plans for effective database maintenance

– Consistency-checking strategy

– Backup strategy

– Index and statistics maintenance strategy

3. Tempdb scalability improvement

– Now provides a separate tab for tempdb files configuration.

SQL Server 2016 CTP 2.4 Setup 02

– optimistic latching of system pages when dropping temp tables.

 

–> Integration Services (SSIS) improvements:

1. Complex data feed including Data Streaming destination.

2. Balanced data distributor (BDD).

3. Excel 2013 source and destination (requires Access runtime 2013).

4. AlwaysOn support.

5. oData v4 source component in addition to oData v3.
 

–> Reporting Services (SSRS) improvements:

– Design-time control over layout of report parameters

– Export to PowerPoint

– A new printing feature that works across modern browsers.

– Check more at: http://blogs.msdn.com/b/sqlrsteamblog/archive/2015/09/30/more-reporting-services-enhancements-in-sql-server-2016-ctp-2-4.aspx
 

–> Deprecation:

– x86 Server deprecation, although all the x86 client tools and components are still available.
 

For all other new features released in SQL Server 2016, please check my blog posts here.
 

So, download the Preview today and start playing with the new features and plan your DB migration/upgrade.
 

Check the [SQL Server blog] for all these updates in detail.
 


 

Check & Like my FB Page.