Issue time12:07:00 pm, by tom.green Email 7998 views
Categories: Performance & Tunning, Indexes, SQL Server

Problem

Displaying the Estimated Execution Plan of a query takes a long time to complete.

 

Solution

Although, the query is pretty complex, displaying the execution plan should not take more than few seconds. In my case it took over 20 minutes to have the estimated execution plan displayed.

After trying different things like updating the statistics or dropping the automatically generated system statistics, the only thing that worked for me was to use a custom query that generates the drop and create statements for the _WA_Sys statistics:

-- delete and create system statistics for several tables in the database

USE DATABASE_NAME --database name

SELECT
N'DROP STATISTICS [' + schema_name(t.schema_id)+N'].['+ t.name + N'].[' + s.name + N']
CREATE STATISTICS [' + c.name + N'] ON [' + schema_name(t.schema_id)+N'].['+t.name + N'] ([' + c.name + N']) WITH FULLSCAN'
FROM sys.stats s (NOLOCK)
INNER JOIN sys.stats_columns (NOLOCK)sic ON sic.stats_id=s.stats_id AND sic.object_id=s.object_id
INNER JOIN sys.tables t (NOLOCK) on t.object_id = s.object_id
INNER JOIN sys.columns c (NOLOCK) on c.column_id = sic.column_id and c.object_id = sic.object_id
WHERE s.name like '%_WA_Sys%'
		and t.name	IN ('TABLE_NAME_1',  -- specify here the name of the table one
						'TABLE_NAME_2',  -- specify here the name of the table two
						'TABLE_NAME_3')  -- specify here the name of the table tree
ORDER BY t.name


-- delete and create system statistics for all tables in the database

USE DATABASE_NAME --database name

SELECT
N'DROP STATISTICS [' + schema_name(t.schema_id)+N'].['+ t.name + N'].[' + s.name + N']
CREATE STATISTICS [' + c.name + N'] ON [' + schema_name(t.schema_id)+N'].['+t.name + N'] ([' + c.name + N']) WITH FULLSCAN'
FROM sys.stats s (NOLOCK)
INNER JOIN sys.stats_columns (NOLOCK)sic ON sic.stats_id=s.stats_id AND sic.object_id=s.object_id
INNER JOIN sys.tables t (NOLOCK) on t.object_id = s.object_id
INNER JOIN sys.columns c (NOLOCK) on c.column_id = sic.column_id and c.object_id = sic.object_id
WHERE s.name like '%_WA_Sys%'
ORDER BY t.name

 

Full story »


Leave a comment
Issue time03:25:00 pm, by tom.green Email 17364 views
Categories: Errors, Server Configuration, SQL Server

Problem

When trying to install or uninstall SQL Server, the setup process fails with the following message:

Rule "Restart Computer" failed.
A computer restart is required. You must restart this computer before installing SQL Server.

This happens because there are pending restart operations on the machine and the setup needs a reboot before continuing. The problem is, that sometimes, even if the computer is restarted, the setup process can fail at exactly the same step.

 

Solution

Check if there are pending rename file operations that needs to be done after the computer is restarted.

To do this open a registry editor (Start -> Run -> Regedit) and navigate to the following path:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\

 

Full story »


Leave a comment
Issue time04:53:00 pm, by tom.green Email 13370 views
Categories: Errors, Security, SQL Server

The following error is received when a user that is not sysadmin is trying to execute the xp_cmdshell stored procedure:

exec xp_cmdshell 'dir c:'

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

 

Use the bellow code to grand execute permission to that user on the xp_cmdshell stored procedure:

USE [master]
CREATE USER [username] FOR LOGIN [username]
GRANT
      Execute
      ON [dbo].[xp_cmdshell]
      TO [username]

 

After, trying again to execute the xp_cmdshell as the non-sysadmin user, the following error appears:

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

 

This happens because when the xp_cmdshell stored procedure is executed by a non-sysadmin user, it connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account## which doesn?t exits.

 

Full story »


1 comment
Issue time04:56:00 pm, by tom.green Email 7380 views
Categories: SSRS, Errors

Problem

I seem that, you cannot display the table header in an every page of a SSRS report, even if you check the Repeat header columns on each page option in the Tablix properties (to open the table properties in Report Builder, right click on that specific table, and choose Tablix Properties).

 

Solution

To solve this, follow the below steps:

Make sure you check the Repeat header columns on each page option in the Tablix properties.

Go to the Grouping pane (the bottom section of the report), click the small arrow from the upper-right corner, and click Advance (the static properties are now displayed).

 

Full story »


Leave a comment
Issue time03:49:00 pm, by tom.green Email 7197 views
Categories: SSRS, Errors

Problem

I seem that, when you scroll through a report in SSRS 2008, you cannot keep the column headers or the row headers visible even if you check the Keep Header Visible While Scrolling option in the Tablix properties (to open the table properties in Report Builder, right click on that specific table, and choose Tablix Properties).

Solution

This problem occurs because the properties used to keep the column or row headers visible on every page have changed from SQL Server 2005 to SQL Server 2008.

 

Full story »


1 comment
August 2017
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

Search

Email Subscription

Enter your email address:

Translate

Advertising











XML Feeds

DBA Blog

Latest posts

SSAS More...
SSRS More...
Server Configuration More...
SQL Server More...
SSIS More...
Server Configuration More...
Errors More...

Poll

Which database platform do you use?

View Results

Powered by b2evolution