Showing posts with label DBA. Show all posts
Showing posts with label DBA. Show all posts

Thursday, 21 September 2017

Quickly find a cached execution plan of an Ad-Hoc query

It is clear that sometimes we just need to find a cached execution plan as quickly as possible so that it can be analysed and then use it to optimise the code in question. This work might be harder if the workload of a database server is based on ad-hoc queries because they do not reuse cached plan as good as stored procedures do especially if Ad-Hoc queries work with parameters, so in this context, we might find many cached plans for only one Ad-Hoc query and it will then make more difficult spot what we are looking for. It is of paramount importance to remember that optimisation and tuning is a never-ending task and it is worthy of our attention every so often. Today I am coming with the following script to help quickly find a cached plan execution plan for an ad-hoc query. This query filters out by the specific text you are looking for in the ad-query.

SELECT TOP 10 st.text AS [SQLText], cp.cacheobjtype CacheObjType, cp.objtype ObjType,
COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [DatabaseName], cp.usecounts AS [PlanUsage], qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = 'dbid'
AND st.text LIKE '%some text of the object or ad-hoc query%'

It well worth noting that this is why it is highly advisable using stored procedures so that CPU and memory resources can be used more efficiently. That is all for now, let me know any remarks you may have. Stay tuned.

Tuesday, 12 September 2017

Detecting poor cursor usage in SQL Server

Without a shadow of a doubt, poor cursor usage issue is an essential aspect to review so as to reduce the possibility of having CPU bottleneck issues, and determine whether cursors are the most appropriate means to accomplish the processing or whether a set-based operation is possible. It is well know that set-based operation is generally more efficient, but if you make the decision of using cursors, you should ensure that it does not represent an issue for the database in question. Thus, detecting poor cursor usage and taking certain measures to ease the problem is crucial, and in SQL Server there are some techniques available to be used to diagnose the issue. To begin with, by using performance counters we are able to analyse the issue and find out the extent to which poor cursor usage is adversely affecting on the performance of specific workloads or the whole database. For instance, here is the counter SQL Server: Cursor Manager By Type: Cursor Requests/Sec which retrieves information about the number of SQL cursor requests received by the server. On the top of that, it is possible to filter out by the cursor manager instance such as API Cursor (only the API cursor information), TSQL Global Cursor and TSQL Local Cursor. Have a look at SQL Server, Cursor Manager by Type Object to get more info about the counter.
On the other hand, using SQL Trace is also helpful, for example, use a trace that includes the RPC:Completed event class search for 'sp_cursorfetch' statements. The value of the fourth parameter is the number of rows returned by the fetch. It is worth noting that the maximum number of rows that are requested to be returned is specified as an input parameter in the corresponding RPC:Starting event class. Finally, by using the DMV 'sys.dm_exec_cursors' we can also determine whether poor cursor usage exists in the database server as Transact-SQL cursors always have a fetch buffer of 1 and for API cursors it should be higher.

select c.* 
from sys.dm_exec_sessions s
    cross apply sys.dm_exec_cursors(con.session_id) as c
where cur.fetch_buffer_size = 1 
    and LIKE 'API%'

Consequently, if it is seen that API Cursors have a fetch buffer size of 1 then consider enabling multiple active results (MARS) when connecting to SQL Server and consult the appropriate documentation for your specific API to determine how to specify a higher fetch buffer size for the cursor either ODBC (SQL_ATTR_ROW_ARRAY_SIZE) or OLE DB (IRowset::GetNextRows, IRowsetLocate::GetRowsAt). After that, we can retrieve more details about the session and connection of the users associated to the harmful cursors so as to decide what to do.

select s.session_id, cn.client_net_address, s.login_name, s.status,s.client_interface_name, s.program_name, 
       c.cursor_id,,, c.plan_generation_num, c.creation_time, c.is_open, c.fetch_status, 
       c.fetch_buffer_size, c.worker_time, c.reads, c.writes, c.dormant_duration
from sys.dm_exec_connections cn
inner join  sys.dm_exec_sessions s on cn.session_id = s.session_id
cross apply sys.dm_exec_cursors(s.session_id) as c
where c.fetch_buffer_size = 1 
     and LIKE 'API%'

That is all for now, thanks for reading. Let me know any remarks you may have. Stay tuned.

Thursday, 7 September 2017

Getting information about memory used by SQL Server

Naturally, every DBA is asked to report information about the memory used by SQL Server. Common questions are related to memory reserved and memory used currently, and sometimes we might not know how to complete this task. How many times did we take a look at the Windows Manager Task to find out that info? Many of us might have ended up quite frustrated time and time again because it did not help much. Over time Microsoft decided to provide Administrators with more useful tools and released Resource Manager tool whereby useful memory information per process is available, nevertheless, whether we want to get that info from SQL Server it might be an uphill battle as it would need that we write certain complex code at windows level.  For the time being, thinking about this situation I made the snap decision of sharing with you some helpful scripts that will alleviate the pain.

In the likely event that you might need to get the total buffer pool memory used by all databases at SQL instance level, this script is for it.

SELECT cast( cast( COUNT(*) /128.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB'  AS TotalUsageBufferPool
FROM sys.dm_os_buffer_descriptors

And if you wanted to know the buffer pool memory used by each database, you can use this:

SELECT CASE database_id 
        WHEN 32767 THEN 'ResourceDb'  ELSE db_name(database_id)         END AS DatabaseName,
        cast( COUNT(*) /128.0 as decimal(10,2)) AS [BufferPool(MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY [BufferPool(MB)] DESC

Finally, and more importantly, answering the question about the total memory used by the whole SQL instance:
-- SQL2012/2014/2016/2017
select cast(cast(physical_memory_kb /1024.0/1024.0  as decimal(10,2))  as varchar(10)) + 'GB' TotalPhysicalRAM, 
cast(cast(visible_target_kb /1024.0/1024.0  as decimal(10,2)) as varchar(10)) + 'GB' MaxRAM, -- max memory configure at sql server level
cast(cast(committed_target_kb /1024.0/1024.0  as decimal(10,2)) as  varchar(10)) + 'GB' ReservedRAM,  --memory reserved
cast(cast(committed_kb /1024.0/1024.0  as decimal(10,2)) as  varchar(10)) + 'GB' UsedRAM --memory used currently
FROM sys.dm_os_sys_info

-- for SQL2005/2008/2008R2
SELECT cast(cast(physical_memory_in_bytes /1024.0/1024.0  as decimal(10,2))  as varchar(10)) + 'GB' TotalPhysicalRAM, 
cast(cast(bpool_visible /128.0/1024.0  as decimal(10,2)) as varchar(10)) + 'GB' MaxRAM, -- max memory configure at sql server level
cast(cast(bpool_commit_target /128.0/1024.0  as decimal(10,2)) as  varchar(10)) + 'GB' ReservedRAM,  --memory reserved
cast(cast(bpool_committed /128.0/1024.0  as decimal(10,2)) as  varchar(10)) + 'GB' UsedRAM --memory used currently
FROM sys.dm_os_sys_info

That is all for now. I hope you find these scripts helpful. Let me know any remarks you may have.

Thursday, 31 August 2017

Updating so-called 'column statistics' for the whole SQL Server database

Definitely, statistics in SQL Server are vital to ensure stable database performance as a whole. Every DBA must know the importance of keeping statistics up to date, however, not everyone who is in charge of a database knows much about it, especially, those who do not have this role or are DBA by accident. So, I will just mention that statistics are objects that contain data distribution, density, selectivity, cardinality, etc. for all columns that were at least once queried or linked to indexes. This information helps the SQL optimizer make the right decision of what indexes and resources should use so as to execute and process the queries as fast as possible. Put differently, statistics allow SQL optimizer to create the best possible execution plan to run queries efficiently, otherwise, when statistics are not up to date bad execution plans may be created which will make inefficient use of resources bringing down the performance of the whole database and, even worse the whole server. So, it is a basic task to routinely update statistics at least once a month as part of your database maintenance tasks.
It is worth noting that statistics that linked to indexes (so-called 'index statistics') are full updated automatically when indexes are rebuilt. So, those statistics do not need explicit update. Nevertheless, statistics linked to columns (so-called 'column statistics') still need of an explicit update and this is what we need to do after rebuilding all the indexes. Thinking about it, today I am going to share a script to only run the update of columns statistics:

DECLARE @schema_name varchar(max)      
DECLARE @table_name varchar(max)     
DECLARE @stat_name varchar(max)        
DECLARE @update_stat_cmd varchar(max)        
DECLARE @update_stat_msg_header   varchar(max)      
DECLARE update_stat_cursor CURSOR FOR    
          select  schema_name(o.[schema_id]),  object_name(s1.[object_id]) ,   
          from (  
            select s.[object_id], from sys.stats s  
            left join sys.indexes i on  
            where is null) s1  
          inner join sys.objects o on o.[object_id]=s1.[object_id]  
          where  o.type='U'  
          order by schema_name(o.[schema_id]),  object_name(s1.[object_id]) ,   
OPEN update_stat_cursor        
FETCH NEXT FROM update_stat_cursor INTO  @schema_name, @table_name,  @stat_name         
WHILE (@@fetch_status = 0)        
       DECLARE @ini DATETIME, @fin DATETIME     
       SET @update_stat_msg_header =  '->Updating ['+ RTRIM(@schema_name)  +'].[' + RTRIM(@table_name) + ']([' +@stat_name + '])'      
       PRINT @update_stat_msg_header    
       SET @update_stat_cmd ='UPDATE STATISTICS ['+ RTRIM(@schema_name)  +'].[' + RTRIM(@table_name) + ']([' +@stat_name + '])'    
       SET @ini=GETDATE()    
       EXEC (@update_stat_cmd)      
       SET @fin=GETDATE()    
       FETCH NEXT FROM update_stat_cursor INTO @schema_name, @table_name,  @stat_name            
PRINT ' '       
PRINT '----------------------------------------------------------------------------- '        
SET @update_stat_msg_header = '*************  THERE ARE NO MORE STATISTICS TO BE UPDATED **************'         
PRINT @update_stat_msg_header        
PRINT ' '        
PRINT 'All statistics not linked to any index were rebuilt!'        
CLOSE update_stat_cursor        
DEALLOCATE update_stat_cursor    

That is all for now. Thanks for reading. Let me know any remarks you may have. Stay tuned.

Monday, 21 August 2017

Dealing with physical database corruptions

Beyond all doubt, not every single database in the world has a DBA dedicated to monitoring it 24x7 hours. Moreover, many database environments are unwittingly implemented/installed where there was no consideration to meet the basic software and hardware minimum requirements. This situation is compounded by the fact that many companies do not put much attention on databases from the beginning, maybe because at first the database are quite small and serving not many transactions per second. Unsurprisingly, all databases are becoming bigger and bigger with the passing of time so it is not rocket science to foresee that everything will get worse in terms of performance and physical integrity. In this new context, it is much more critical to have everything in place so as to prevent databases from getting damaged. However, many times it is too late when one realises that the database got damaged because of poor implementation. The overwhelmingly majority of physical corruption issues are not sparked by SQL Server on its own, but poor hardware implementation. Thus, if you find yourself working with databases struggling this problem, I wholeheartedly recommend reinstalling the whole server from scratch taking minimum requirements on board.
After having a good implementation of a database server, it is of paramount importance to carry out maintenance tasks at least once a month, and it should include executing full DBCC CHECKDB, and if possible DBCC CHECKDB WITH PHYSICAL_ONLY option once a week. It is also understandable that DBCC CHECKDB makes intensive use of resources, especially CPU and Disk, nevertheless it is possible to use MAXDOP option with DBCC CHECKDB to limit CPU usage and ease the pain. So, nowadays there is no feasible excuse to avoid executing it regularly.

Having said that, I would like to share with you some steps to follow in order to try to fix a physical database corruption:

1. To begin with, try to back your database up.
2. Set your database to SINGLE_USER mode, and then execute DBCC CHECKDB WITH PHYSICAL_ONLY to determine whether physical corruption exists or not.


If there is physical corruption, SQL Server will display some errors like these:

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown),
page ID (1:219356) contains an incorrect page ID in its page header.
The PageId in the page header = (0:0).
Msg 8928, Level 16, State 1, Line 1
Object ID 501576825, index ID 1, partition ID 72057594040549376,
alloc unit ID 72057594044940288 (type In-row data): Page (1:1408252) could not be processed.

3. Sometimes only non-clustered indexes are damaged, and luckily in this case, you just need to recreate those indexes. If you want to figure out which indexes are damaged so that you can recreate them, you can have a look at this article I wrote many years ago.
4. However, if the clustered index or heap structure is damaged then you may need set the database to EMERGENCY mode and execute DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option which may not always be the best option for bringing a database to a physically consistent state but when the clustered index or heap structure of a table is heavily damaged there is no other option, just do it at the cost of some data loss. It is also worth noting that if you want to find out which objects are damaged so that only execute DBCC CHECKDB repair process on them then you can also check out the same article. Here is this example, we are going to execute on the whole database.


5. Having executed DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option does not always ensure that the database will be repaired, however, many times it is enough. So, after getting your database repaired, try to go to bottom of the problem, and fix the problem at hardware level because it is most likely that disk storage is not working properly. Bearing in mind that database corruption issue is just the tip of the iceberg so consider DBCC CHECKDB as a quick fix, not a final solution.
6. Finally, you should take a full database backup.

That is all for now, I hope you find this post helpful. Let me know any remarks you may have. Stay tuned.

Tuesday, 15 August 2017

Altering SQL Jobs without granting SysAdmin privilege

What would you do if you were asked to grant a few users the permission for altering SQL Jobs? It is a tricky task to carry out although many of us would think that granting SQLAgentOperatorRole role might be enough, however, many years ago no sooner had I done it than I realised it did not work as expected, and now I think most of us faced up this issue at first.
The SQLAgentOperatorRole role allows user to alter SQL jobs as long as the user is the owner of the SQL job, otherwise, the user need to be SysAdmin at SQL instance level. What’s more, one of the best security practices says that nobody but DBA must be SysAdmin, and we should use Windows Authentication. Nevertheless, when it comes to owners for SQL jobs they should use 'sa' as owner which does not mean having enable that account, it should be disable. In this sense, it is highly advisable to have the disabled account “sa” as the owner of all SQL jobs and avoid granting SysAdmin privilege. So, it is of paramount importance not to use a windows user as owner of a SQL job because SQL Server will always validate windows users against the Active Directory and it is likely to get unforeseen errors during that process. 
Today I am going to share with you a stored procedure to enable users to alter SQL Jobs without the need of granting SysAdmin privilege. This stored procedure consists of a logic that will allow a specific user to take the ownership of a SQL Job so that the user can be able to alter it and after making the changes the user can change the ownership to ‘sa’ (or the original owner). This is the stored procedure that I mentioned above.

USE [msdb]
CREATE PROC[dbo].[usp_change_owner_job] @jobname varchar(max), @newowner varchar(max)
with execute as owner
    declare @old_owner varchar(max)
    select @old_owner= from msdb.dbo.sysjobs j inner join sys.server_principals s
    on j.owner_sid= s.sid where

    --declare @newowner varchar(max)
    --set @newowner=ORIGINAL_LOGIN()
    EXEC msdb.dbo.sp_update_job @job_name=@jobname, @owner_login_name=@newowner

    --print @newowner
    declare @msg varchar(max)
    set @msg= 'The owner of ' +@jobname + ' job was changed from ''' + @old_owner + ''' to ''' + @newowner + ''''
    print @msg    

So, for instance, if you have a SQL job whose owner is 'sa' and your user is 'User2', you will not be able to alter the job until you take the ownership of it temporarily. Here are all the steps you must follow:

1. Create the stored procedure 'usp_change_owner_job' based on the code above.
2. Grant EXECUTE permission on 'usp_change_owner_job' to 'User2'.
3. Grant SQLAgentOperatorRole role to 'User2'.
4. Change the ownership of the SQL Job to 'User2' by using the stored procedure 'usp_change_owner_job'.

EXEC msdb.dbo.[usp_change_owner_job] 'BusinessJob01', 'User2'

The owner of BusinessJob01 job was changed from 'sa' to 'User2'

5. Now the User2 has the ownership of the SQL job and is now able to alter it.
6. After making the changes on the SQL Job, the 'User2' must change the ownership to 'sa'.

The owner of BusinessJob01 job was changed from 'User2' to 'sa'

The owner of BusinessJob01 job was changed from 'User2' to 'sa'

Having successfully completed all the steps will you be able to alter any SQL Job without granting SysAdmin privilege to users. That is all for now. Let me know any remarks you may have.

Friday, 4 August 2017

Troubleshooting timeout expired errors

Having .NET Applications working with SQL Server, some unforeseen timeout errors can be raised for two possible causes: bad-written SQL code (especially Ad-Hoc queries) and application issues. Under this circumstance, developers will generally blame SQL Server at first glance because they may think it is a misconfiguration related to timeout in SQL Server. In my experience troubleshooting these issues, I would say that lots of them are not mostly related to SQL Server configuration itself. Additionally, when it comes to application as the root cause, reading the SQL Server error log, you will not find any error about timeout or failed login events. What does it mean? it simply means that the application never tried to connect to SQL Server because it was still working at application level when the error was raised. Thus, the cause of timeout error is inside the application at Net SqlClient Data Provider level (see the CommandTimeout property value configured for the application connection). This 'CommandTimeout' property specifies the number of seconds that a application provider should wait for result sets before sending a timeout signal. It is well known that the default is 30 seconds, therefore, it may not be good enough due to application performance problems.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
In order to get it fixed, firstly, developers should check .NET code within applications. In some cases, the developers I worked with found infinite loops or slow codes which spent much time before sending SQL queries to the database engine, as a result of this, the timeout threshold was reached at application level and, obviously, the error was raised. By the way, the following error could also be related to timeout:
Unable to connect to SQL Server session database.
Having checked the application, you can increase the 'CommandTimeout' property value to 60 seconds (if it is not enough then change to 120 or 180, and also considerate using 0 (unlimited) which should be a workaround while the problem is being traced and fixed). That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.

Tuesday, 25 July 2017

Testing database connectivity by using a Universal Data Link file

After installing a SQL instance, we may need to test the database connectivity from a client host to make sure that everything is working very well, for instance, sometimes Windows Firewall might block access to the service or there might be network issues. Moreover, in the likely event that there was no time to install SQL Server client tools such as SSMS or SQLCMD to carry out the test, you woud be a bit suprised to know that there is a simpler way to do it, that is via a Data Link file. Consequently, in this post I am going to show you how to create and use a Data Link file to test connectivity to a SQL instance. To begin with, you must open Notepad to create an empty .txt file and save it with the .udl extension as you can see in the following picture.
After doing that, you must open the .udl file and you will then see the following window with four tabs. The second tab "Connection" is to fill with the server name (or SQL instance name) and the credentials accordingly. For instance, I am testing the connectivity to a default SQL instance and using Windows Authentication. You must modify that to serve your needs.
In the first tab "Provider" we can choose the Provider to use in the test. By default, it is always "Microsoft OLE DB Provider for SQL Server". It is ideal to test other providers as well. It is worth noting that SQL Native Providers will be listed if SQL Client tools are installed locally in the client host from where the test is done. In the tab "Advanced" it is possible to set the timeout value whereas in the tab "All" we can see a summary of all the settings, and we can also edit the values of some important connection parameters such as "Language", "Connect Timeout", "Packet Size", "Data Source" and "Initial Catalog".

Finally, we just have to click on "Test Connection" in the tab "Connection" to proceed with the test. If the connectivity to the SQL instance is ok, you will see the message "Test connection succeeded", it couldn't have been simpler!
That is all for now. I hope you find this post helpful and practical. Let me know any remarks you ma have. Stay tuned.

Thursday, 20 July 2017

Detecting excessive compilation and recompilation issues

Undoubtedly, recompilation is a big topic to reckon with, more importantly, in database environments processing data that is changing rapidly over time and compounded by ad-hoc workloads which may cause CPU bottleneck, so it is of paramount importance to detect excessive compilation and recompilation issues and address them to warrant stable performance for the queries, and in this sense, there are some tools to be used to detect these issues such as Performance Monitor, Extended Events, SQL Server Profiler Trace, DMVs, etc. When it comes to using Performance Monitor we should concentrate the efforts on analising the performance counters SQL Server: SQL Statistics: Batch Requests/sec, SQL Server: SQL Statistics: SQL Compilations/sec, and SQL Server: SQL Statistics: SQL Recompilations/sec. What's more, you can save a trace file capturing the events SP:Recompile, SQL:StmtRecompile, and CursorRecompile, then you can use the following query to see all the recompilation events:

select spid, StartTime, Textdata, EventSubclass, ObjectID, DatabaseID, SQLHandle 
from fn_trace_gettable('C:\RecompilationTrace_01.trc', 1)
where EventClass in(37,75,166) -- 37 = Sp:Recompile, 75 = CursorRecompile, 166 = SQL:StmtRecompile

Furthermore, we can also capture the showplan XML for query compile, but doing that has significant performance overhead because it is captured for each compilation or recompilation. So, just do it for a very short time as long as you see a high value for the SQL Compilations/sec counter in Performance Monitor. Once you know where the problem is you can use Engine Tuning Advisor to see whether any indexing changes improve the compile time and the execution time of the query.
Talking of DMVs to diagnose recompilation issues, looking into 'sys.dm_exec_query_optimizer_info' is very helpful, in particular, look at the Elapsed Time, which is the time elapsed due to optimizations, and also Final Cost. If you see that Time Elapsed is very close to the CPU time, you might reach the conclusion that the compilation and recompilation time is attributable to that high CPU use. Another DMV to use is 'sys.dm_exec_query_stats' whose most important columns to look at are sql_handle, total worker time, plan generation number (the number of times the query has recompiled), and statement Start Offset. Here is an example to check the top 20 most procedures that have been recompiled.

select top 20 SQLText.text, sql_handle, plan_generation_num, execution_count, dbid, objectid 
from sys.dm_exec_query_stats
   cross apply sys.dm_exec_sql_text(sql_handle) as SQLText
where plan_generation_num >1
order by plan_generation_num desc

There is a plethora of recommendations to deal with recompilation and keep the performance in optimum conditions, but for now you can take into consideration the following options:
  • Check to see whether the stored procedure was created with the WITH RECOMPILE option or whether the RECOMPILE query hint was used. If a procedure was created with the WITH RECOMPILE option, since SQL Server 2005, you may be able to take advantage of a statement-level RECOMPILE hint if a particular statement within that procedure needs to be recompiled. Using this hint at the statement level avoids the need of recompiling the whole procedure each time it executes, while at the same time allowing the individual statement to be compiled. 
  • Recompilations can occur due to changes in statistics, and you can use the KEEPFIXED PLAN query hint to make recompilations occur only when there is the need to ensure correctness and not to respond to changes in statistics. For instance, in this context recompilation can only occur if the underlying table structure or its schema that is referenced by a statement changes, or if a table is marked with the sp_recompile stored procedure, all resulting in the fact that the plan no longer applies and consequently triggering the recompilation event. 
  • Using the KEEP PLAN query hint is useful to set the recompilation threshold of temporary tables to be the same as permanent tables. Take a look at the EventSubclass column which displays 'Statistics Changed' for an operation on a temporary table.
  • Turning off the automatic updates of statistics for indexes and statistics that are defined on a table or indexed view prevents recompilations that are due to statistics changes on that object. It is worth noting that turning off the auto-stats option is not always a good idea. This is because the query optimizer is no longer sensitive to data changes in those objects resulting in suboptimal query plans. To be honest, I never turned off this option because I always preferred trusting in SQL Server criteria and instead opting to work on optimising queries.
  • Keep in mind that recompilation thresholds for temporary tables are lower than for normal tables, so if the recompilations on a temporary table are due to statistics changes, you can change the temporary tables to table variables. A change in the cardinality of a table variable does not cause a recompilation. The side effect of this approach is that the query optimizer does not keep track of a table variable's cardinality because statistics are not created or maintained on table variables. This can result in less optimal query plans, however, you can test the different options and choose the best one. Generally, temporary tables provide much better performance than tables variables when lots of data is involved.
  • Recompilation might also occur as a result from SET option changes, so one can diagnose it by using SQL Server Profiler to determine which SET option changed. It is highly advisable to avoid changing SET options within stored procedures and it is much better to set them at the connection level, and for the overwhelmingly majority of cases the default SET options work well. It is also very important to ensure that SET options are not changed during the lifetime of the connection.
  • To avoid recompilations that are due to deferred compilations, do not combine DML with DDL and do not create the DDL as a result from conditional IF statements.
  • To avoid recompilation and also to avoid ambiguity between objects, batches should have qualified object names, for example, dbo.Table1, User1.MySP, etc.
That is all for now, let me know any remarks you may have.

Friday, 14 July 2017

Ports and Protocols Used by Microsoft SQL Server

Naturally, I have been asked many times about ports used by SQL Server services and to be honest sometimes I took me some time to reply because there is a great number of ports and protocols and it is not easy to remember them quickly. Not all of us have to learn everything by rote, so thinking about it, I made the decision of sharing the following lists of useful ports and protocols so that you can have them at hand when needed.

Ports and Protocols Used by Microsoft SQL Server 2000
Service / Purpose Protocol Port
Analysis Services TCP 2725
Client connections when "hide server" option enabled TCP 2433
Clients using Named Pipes over Netbios TCP 139/445
Microsoft SQL Monitor port UDP 1434
OLAP Services connections from downlevel clients OLAP Services 7.0 TCP 2393/2394
SQL over TCP ** TCP 1433
Standard URL for a report server (Reporting Services) TCP 80 HTTP /443 SSL
Ports and Protocols Used by Microsoft SQL Server 2005
Service / Purpose Protocol Port
Analysis Services connections via HTTP (default) TCP 80
Analysis Services connections via HTTPS (default) TCP 443
Clients using Named Pipes over Netbios TCP 137/138/139/445
Dedicated Administrator Connection TCP 1434 by default (local port). But this port is assigned dynamically by SQL Server during startup.
Reporting services on Windows 2003/2008/Vista (default) TCP 80
Reporting services on Windows XP SP2 TCP 8080
SQL Server 2005 Analysis Services TCP 2383
SQL Server Browser Service TCP 2382
SQL Server Integration Services (MSDTSServer) TCP 135
SQL Server Resolution Protocol TCP 1434
SQL over TCP (default instance) TCP 1433
SQL over TCP (named instances) TCP 1434 / 1954
Ports and Protocols Used by Microsoft SQL Server 2008/2012/2014/2016/2017
Service / Purpose Protocol Port
Analysis Services connections via HTTP (default) TCP 80
Analysis Services connections via HTTPS (default) TCP 443
Clustering UDP 135
Clustering TCP 135 (RPC) / 3343 (Cluster Network Driver) / 445 SMB / 139 NetBIOS / 5000-5099 (RPC) / 8011-8031 (RPC)
Database Mirroring TCP There is no default port for this service. Use the following T-SQL statements to identify which ports are in use: SELECT name, port FROM sys.tcp_endpoints.
Dedicated Administrator Connection TCP 1434 by default (local port). But this port is assigned dynamically by SQL Server during startup.
Filestream TCP 139 y 445
Microsoft Distributed Transaction Coordinator (MS DTC) TCP 135
Reporting services Web Services TCP 80
Reporting Services configured for use through HTTPS TCP 1433
Service Broker TCP 4022
SQL Server Analysis Services TCP 2382 (SQL Server Browser Services for SSAS port)
2383 (Clusters will listen only on this port)
SQL Server Browser Service (Database Engine) UDP 1434. Might be required when using named instances.
SQL Server Browser Service TCP 2382
SQL Server default instance running over an HTTPS endpoint. TCP 443
SQL Server Instance (Database Engine) running over an HTTP endpoint.  TCP 80 y 443 (SSL)
SQL Server Integration Services TCP 135 (DCOM)
SQL over TCP (default instance) TCP 1433
Transact-SQL Debugger TCP 135
Windows Management Instrumentation TCP 135 (DCOM)

That is all for now. I hope you find this post useful. Let me know any remarks you may have. Stay tuned.

Monday, 3 July 2017

Getting important information of SQL Server Backups

Regardless of the method that is used to take SQL Server backups, they need to be carefully monitored 24x7 hours especially in critical database environments whose data is changing rapidly. Keeping the backups up to date is crucial as they can save us a lot of time when it comes to recovering databases in the likely event of unwitting incidents. On the top of that, in my experience it was shameful to see many environments without a backup strategy in place, even more, nobody was in charge of them.  In that context, it is clear that those businesses were surely at high risk of losing a lot of data and jaw-dropping amounts of money. Sometimes it is believed that having backups of the whole virtual machine is good enough, nevertheless, this sort of backup has a different purpose from a database backup. Thus, at no time can we replace SQL Server database backups with virtual machine backups which are time-consuming, inadequate, far-fetched for databases. In this sense, having implemented a suitable database backup strategy (Full + Diff + Log Backups) and carefully monitoring them are fundamental tasks to serve the needs of ensuring the recovery database process.
Broadly, it is of paramount importance to have a script at hand to find out whether or not specific sorts of backups were taken properly as well as how long they last, the size of the backup file, who took the backup, or if the backup was taken using CHECK_SUM option, etc. Today I am sharing that script which returns important information of backups for all databases in a SQL Server instance. In the following script, it filters out info of Full Backups (type='D'). You can use 'L' for Log Backup and 'I' for Differential Backup. So, try it out and make the most out of the info.

SELECT isnull(,'') DatabaseName,
CASE is_read_only WHEN 1 THEN 'YES' ELSE 'NO' END as IsReadOnly, 
cast(sum(size)/128.0/1024.0 as decimal(8,2) ) as DBSize_InGB ,  
isnull( last_backup.recovery_model,'')RecoveryModel,isnull(,'') as BackupName ,
isnull(cast(last_backup.backup_start_date as varchar(24)),'(No Taken)') as BackupStartDate,  
isnull(case when [backup_size]>1024 then  cast( cast([backup_size]/1024.0 as decimal(8,2)) as varchar(12)) 
     + 'GB' else cast([backup_size] as varchar(12)) + 'MB' end,0) as BackupSize,
isnull(case when compressed_backup_size>1024 then  cast( cast(compressed_backup_size/1024.0 as decimal(8,2)) as varchar(12)) +
     'GB' else cast(compressed_backup_size as varchar(12)) + 'MB' end,0) as CompressedBackupSize ,
 isnull([duration],'') as Duration, isnull(is_copy_only,'') as IsCopyOnly, isnull(is_damaged,'') as IsDamaged,  
 isnull(has_backup_checksums,'') as HasBackupCheckSUM, isnull([user_name],'')as UserName
FROM master.sys.sysaltfiles  f
    inner join sys.databases d on f.dbid=d.database_id
    inner join (select database_name,backup_start_date, name, is_damaged,is_password_protected,
                has_incomplete_metadata,is_copy_only, cast(backup_size/1024.0/1024.0 as decimal(8,2))  as [backup_size], 
                cast(compressed_backup_size/1024.0/1024.0 as decimal(8,2))  as compressed_backup_size,
                isnull(cast(  CAST((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))/3600 AS varchar) + ' hours ' 
                     + CAST(((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))%3600 )/60 AS varchar)+ ' min'    as varchar(40) 
                ), '(No Available)') as [duration],bs.backup_finish_date, has_backup_checksums , [user_name], recovery_model
              FROM msdb.dbo.backupset bs
              WHERE bs.backup_set_id IN (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
                                                      where    type = 'D'
                                                     GROUP BY database_name) 
                AND bs.database_name IN (SELECT name FROM master.dbo.sysdatabases  )
                                ) as last_backup   on = last_backup.database_name
                             where fileid<>2    
                             group by dbid, , d.is_read_only,d.recovery_model_desc, last_backup.database_name, 
last_backup.backup_start_date,last_backup.[backup_size] ,last_backup.compressed_backup_size,, last_backup.duration,
 last_backup.backup_finish_date,is_copy_only,is_damaged,has_backup_checksums , [user_name], last_backup.recovery_model
ORDER BY DATEDIFF(minute,  last_backup.backup_start_date,  last_backup.backup_finish_date) desc

That is all for now, let me know any remarks you may have.