Protected: document: SP_who2

This content is password protected. To view it please enter your password below:

Password:

Advertisements

Protected: Windows Cluster Configuration steps

This content is password protected. To view it please enter your password below:

Password:

DATA and PAGE Compression in SQL Server

There are two types of data compression in SQL Server.

• ROW Compression. This compression feature takes into account the variable data type structures defining a column. Take, for instance, a CHAR(100) column stored in a variable length storage format will only use up the amount of storage defined by the data. Storing “SQL Server 2008 ” in the column will only require storing fifteen characters instead of the full 100 characters, thereby, a 85% savings on storage space. This is the extension of the vardecimal storage format made available in SQL Server 2005 Service Pack 2. Note, also, that this compression feature does not take any disk space for zero or null values.
• PAGE Compression. This compression feature is a superset of ROW compression and takes into account the redundant data in one or more rows on a given page. It also uses prefix and dictionary compression. What this simply means is that for both page compression techniques, the storage engine cuts down on repeated data in the page. For example, if a table is partitioned using a column prefix, all data in a specific partition will have the same or similar prefix. Let’s say the value of columns start with something like A1000Q-xxxx like some product codes, the storage engine store the A1000Q- once on the page and then refer to this value from all other occurrences of this value on the same page. This can also be said of a column with a defined DEFAULT constraint. Page compression only occurs when the page is full to optimize the performance.

Steps:

Right click on the Table ==> Properties==> Storage==> Manage Compression

This will launch the Data Compression Wizard. On the Welcome to Data Compression Wizard, click Next.

On the Select Compression Type page, click the drop-down menu on the Compression Type column to select your compression of choice.

Click the Calculate button to display a similar information provided by the table/stored procedure.

Notes:

1.Data Compression in SQL Server has been made available in SQL Server 2005
with Service Pack 2.
2.SQL Server 2008 supports both row and page compression for both tables and
indexes.
3.PAGE compression feature is a superset of ROW compression.
Page compression only occurs when the page is full to optimize the performance.

Reference: ( For the above information with clear example follow below link)

https://www.mssqltips.com/sqlservertip/1582/implementing-data-compression-in-sql-server-2008/

 

 

 

How to check port 5022 is blocked on a Windows machine?

Run below command in command prompt to verify whether port 5022 is blocked or not.

portqry -n  servername -e 5022

Note: portqry Utility should be available in windows to run the above command

Detailed:

The best way to check if a port is blocked is to do a port scan from the client machine.

There are many ways to do a port scan but since you mentioned being on Windows then I will suggest the Microsoft command line utility PortQry and the Graphical version PortQryUI

Using a PortScan utility you will get one of 3 results.

• Listening means the server is listening on the specified port
• Filtered means it received a TCP acknowledgement packet with the Reset flag set which likely indicates a firewall or software issue
• Not Listening means it didn’t receive a response at all

*************************************************************

Another way to check:

• There is one command to check the firewall state
  (Update for Windows 7 users — as referred by Nick below — use netsh advfirewall firewall)

  netsh firewall show state

*******************SQL Server Error:1418******************

Refer for Workarounds:

1. https://blogs.msdn.microsoft.com/docast/2015/07/30/database-mirroring-configuration-failure-scenarios/

2. http://blog.sqlauthority.com/2010/01/11/the-server-network-address-tcpsqlserver5023-can-not-be-reached-or-does-not-exist-check-the-network-address-name-and-that-the-ports-for-the-local-and-remote-endpoints-are-operational-microso/

3.http://stackoverflow.com/questions/11032937/how-to-resolve-error-1418-in-sql-server-while-mirroring

4.https://msdn.microsoft.com/en-us/library/ms189127.aspx

 

 

 

 

Key reason to use AlwaysON AG instead of Mirroring

Most of the applications are being built where multiple databases make up the application, so if only one of the databases fails over in mirroring the other database will still be functional on the principal server, but the application won’t work.  How can I be notified when this happens and make all of the databases fail-over?

Note: In such situation,for all the application dependent databases, we used to configure                 database mirroring separately for each database.

But again if there is an database specific issue (ex: DB corrupt), fail-over happens                  to that database only but not for all databases, ultimately application wont work.

“One of the big issues with Database Mirroring is that the fail-over is tied to one database” , to over come this we can use Always ON Availability Groups (AG) where we can group multiple databases into one unit and can perform fail-over.

 

******************************************************************

Database Mirroring Automating Failover for Multiple SQL Server Databases

https://www.mssqltips.com/sqlservertip/1564/database-mirroring-automating-failover-for-multiple-sql-server-databases/

Frequently Used five DMV’s

 percent_complete DMV in SQL Server:

SELECT  percent_complete,*  FROM sys.dm_exec_requests

1.  SELECT * FROM sys.dm_exec_requests

– info about each request that is executing (including all system processes)

2. SELECT * FROM sys.dm_exec_sessions

– info about all active user connections and internal tasks

3. sys.dm_exec_connections
— info about connections established

4.sys.dm_db_index_physical_stats (DMF)
— shows index storage and fragmaentation info

5.sys.dm_db_index_usage_stats (DMV)
— shows how often indexes are used and for what type of SQL operation

6.  sys.dm_os_performance_counters

Q.Difference bw DMV and DMF ?

Dynamic Management Objects (DMOs)

1.Dynamic Management Views (DMVs)        — can select like a view

2.Dynamic Management Functions(DMFs) –Requires input parameters like a function

Reference:

Must Read –  http://bidn.com/Blogs/userid/10/top-10-most-useful-sql-server-dmvs-greg-robidoux

https://www.mssqltips.com/sqlservertip/1338/finding-a-sql-server-process-percentage-complete-with-dmvs/

***********************************************************

DBCC:

1. DBCC SQLPERF(logspace)  – To find log file usage and free space information
2. DBCC CHECKDB                       – To check databse integrity/corrupt issue
3. DBCC SHRINKFILE  , DBCC SHRINKDATABASE  — To shrink DB/DBfiles
4. DBCC INPUTBUFFER              –
5. DBCC OPENTRAN                    –
6. DBCC TRACEOFF, TRACEON, TRACESTATUS  –

 

**********Detailed information below**********

1.DBCC CHECKALLOC

DBCC CHECKALLOC checks page usage and allocation in the database. Use this command if allocation errors are found for the database. If you run DBCC CHECKDB, you do not need to run DBCC CHECKALLOC, as DBCC CHECKDB includes the same checks (and more) that DBCC CHECKALLOC performs.
2.DBCC CHECKCATALOG

This command checks for consistency in and between system tables. This command is not executed within the DBCC CHECKDB command, so running this command weekly is recommended.

3.DBCC CHECKCONSTRAINTS
DBCC CHECKCONSTRAINTS alerts you to any CHECK or constraint violations.
Use it if you suspect that there are rows in your tables that do not meet the constraint or CHECK constraint rules.

4.DBCC CHECKDB
A very important DBCC command, DBCC CHECKDB should run on your SQL Server instance on at least a weekly basis. Although each release of SQL Server reduces occurrences of integrity or allocation errors, they still do happen. DBCC CHECKDB includes the same checks as DBCC CHECKALLOC and DBCC CHECKTABLE. DBCC CHECKDB can be rough on concurrency, so be sure to run it at off-peak times.

5.DBCC CHECKTABLE
DBCC CHECKTABLE is almost identical to DBCC CHECKDB, except that it is performed at the table level, not the database level. DBCC CHECKTABLE verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets. DBCC CHECKTABLE uses schema locks by default, but can use the TABLOCK option to acquire a shared table lock. CHECKTABLE also performs object checking using parallelism by default (if on a multi-CPU system).

6.DBCC CHECKFILEGROUP
DBCC CHECKFILEGROUP works just like DBCC CHECKDB, only DBCC CHECKFILEGROUP checks the specified filegroup for allocation and structural issues. If you have a very large database (this term is relative, and higher end systems may be more apt at performing well with multi-GB or TB systems ) , running DBCC CHECKDB may be time-prohibitive.
If your database is divided into user defined filegroups, DBCC CHECKFILEGROUP will allow you to isolate your integrity checks, as well as stagger them over time.

7.DBCC CHECKIDENT
DBCC CHECKIDENT returns the current identity value for the specified table, and allows you to correct the identity value if necessary.

8.DBCC DBREINDEX
If your database allows modifications and has indexes, you should rebuild your indexes on a regular basis. The frequency of your index rebuilds depends on the level of database activity, and how quickly your database and indexes become fragmented. DBCC DBREINDEX allows you to rebuild one or all indexes for a table. Like DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, running DBREINDEX during peak activity times can significantly reduce concurrency.

9.DBCC INDEXDEFRAG
Microsoft introduced the excellent DBCC INDEXDEFRAG statement beginning with SQL Server 2000. This DBCC command, unlike DBCC DBREINDEX, does not hold long term locks on indexes. Use DBCC INDEXDEFRAG for indexes that are not very fragmented, otherwise the time this operation takes will be far longer then running DBCC DBREINDEX. In spite of it’s ability to run during peak periods, DBCC INDEXDEFRAG has had limited effectiveness compared to DBCC DBREINDEX (or drop/create index).

10.DBCC INPUTBUFFER
The DBCC INPUTBUFFER command is used to view the last statement sent by the client connection to SQL Server. When calling this DBCC command, you designate the SPID to examine. (SPID is the process ID, which you can get from viewing current activity in Enterprise Manager or executing sp_who. )

11.DBCC OPENTRAN
DBCC OPENTRAN is a Transact-SQL command that is used to view the oldest running transaction for the selected database. The DBCC command is very useful for troubleshooting orphaned connections (connections still open on the database but disconnected from the application or client), and identification of transactions missing a COMMIT or ROLLBACK. This command also returns the oldest distributed and undistributed replicated transactions, if any exist within the database. If there are no active transactions, no data will be returned. If you are having issues with your transaction log not truncating inactive portions, DBCC OPENTRAN can show if an open transaction may be causing it.

12.DBCC PROCCACHE
You may not use this too frequently, however it is an interesting DBCC command to execute periodically, particularly when you suspect you have memory issues. DBCC PROCCACHE provides information about the size and usage of the SQL Server procedure cache.

13.DBCC SHOWCONTIG
The DBCC SHOWCONTIG command reveals the level of fragmentation for a specific table and its indices. This DBCC command is critical to determining if your table or index has internal or external fragmentation. Internal fragmentation concerns how full an 8K page is.
When a page is underutilized, more I/O operations may be necessary to fulfill a query request than if the page was full, or almost full.
External fragmentation concerns how contiguous the extents are. There are eight 8K pages per extent, making each extent 64K. Several extents can make up the data of a table or index. If the extents are not physically close to each other, and are not in order, performance could diminish.

14.DBCC SHRINKDATABASE
DBCC SHRINKDATABASE shrinks the data and log files in your database.
Avoid executing this command during busy periods in production, as it has a negative impact on I/O and user concurrency. Also remember that you cannot shrink a database past the target percentage specified, shrink smaller than the model database, shrink a file past the original file creation size, or shrink a file size used in an ALTER DATABASE statement.

15.DBCC SHRINKFILE
DBCC SHRINKFILE allows you to shrink the size of individual data and log files. (Use sp_helpfile to gather database file ids and sizes).

16. DBCC TRACEOFF, TRACEON, TRACESTATUS
Trace flags are used within SQL Server to temporarily enable or disable specific SQL Server instance characteristics. Traces are enabled using the DBCC TRACEON command, and disabled using DBCC TRACEOFF. DBCC TRACESTATUS is used to displays the status of trace flags. You’ll most often see TRACEON used in conjunction with deadlock logging (providing more verbose error information).

17.DBCC USEROPTIONS
Execute DBCC USEROPTIONS to see what user options are in effect for your specific user connection. This can be helpful if you are trying to determine if you current user options are inconsistent with the database options.

difference between SQL Cluster installation 2005 and 2008

What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?

• On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…

Fill Factor

The fill factor value determines the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage.

********************************

In other words “fill factor value determines how much data is written to an index page when it is created / rebuilt.”

“Timeout Error” in SQL Server 2012 AlwaysON

Issue: 

1 . “Timeout expired” error when you try to connect to SQL Server 2012 through an AlwaysOn availability group listener.

2. Application “Timeout” Errors in SQL Server

Causes:

Note: The time-out error typically occurs within several seconds of the connection attempt from the application. Frequently, the time within which the error occurs is less than 5 seconds.

If you increase the Connection Timeoutproperty from the default value of 15 seconds to 20 or 30 seconds, the error still occurs. However, if you increase theConnection Timeout property to a much larger value such as 150 or 200 seconds, the connection usually succeeds.

****************************************************************

 

Example:  ” Timeout”  Issue on AlwaysON

When you try to connect to Microsoft SQL Server 2012 through an AlwaysOn availability group listener from a client application that uses the Microsoft .NET Framework Data Provider for SQL Server (System.Data.SqlClient), you may receive the following time-out error message:

“Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

Reasons:

You receive this message when the following conditions are true:

• Your availability group listener is in a single or multiple subnet.
• The application uses the .NET Framework Data Provider for SQL Server that is installed together with one of the following:
  • The Microsoft .NET Framework 3.5 Service Pack 1 (SP1)
  • The Microsoft .NET Framework 4
  • The Microsoft .NET Framework 4.5
• You specify the parameter multisubnetfailover=yes or multisubnetfailover=true in the SQL Server connection string.
• Note: The time-out error typically occurs within several seconds of the connection attempt from the application. Frequently, the time within which the error occurs is less than 5 seconds. If you increase the Connection Timeoutproperty from the default value of 15 seconds to 20 or 30 seconds, the error still occurs. However, if you increase theConnection Timeout property to a much larger value such as 150 or 200 seconds, the connection usually succeeds.

Cause:

The issue occurs because of an issue in the .NET Framework Data Provider for SQL Server.

When you use the parameter multisubnetfailover=yes or multisubnetfailover=true, the provider retries the TCP connections. To implement the retry algorithm, the provider divides the actual Connection Timeout value into small chunks or slices in order to speed up the number of retries. For example, the following are the waiting times when the value of Connection Timeout is set to its default of 15 seconds:

• Waiting time (time slice) for the first connection attempt is 15 * 0.08 ~ 1.2 seconds.
• Waiting time (time slice) for the second connection attempt is 15 * 0.16 ~ 2.4 seconds.

However, if a response is slow and the connection is not completely established before the waiting time expires, the data provider incorrectly sets the connection to a doomed state. Additionally, the provider incorrectly throws a time-out error after the expiration of the waiting time (time slice) instead of the actual or full Connection Timeout value.

Note The slow response in establishing a connection may in this case be triggered either by the server or by network latency.

*************************************************************

Timeout errors are client side and the error is coming from the provider (or client) being used with the database connection. SQL Server does not necessarily keep track or offer any intuitive method to track these down.

**************************************************************

Blocking could certainly contribute to timeouts and can be fairly easily tracked. A timeout that is not related to a block has some other issue. The 30 second timeout is a common clientsetting, but it can be controlled by the command object. If you set it to 0 then the connection will not timeout.

 

Resolution:

Work around:

Method 1 (recommended)

Set the Connection Timeout or Connect Timeout property to a large value such as 200. When you do this, the data provider sets the waiting time (time slice) for the first connection attempt to 200 * 0.08 ~ 16 seconds.
Change Connection string to increase time out:

Data Source=SomeDBServer;Initial Catalog=SomeDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Connect Timeout=60;

Method 2

If you use a SQL Server 2012 AlwaysOn availability group listener in a single subnet scenario, you can either remove the parameter multisubnetfailover=true from the connection string or use the parameter multisubnetfailover=false ormultisubnetfailover=no.

MS Solutions: https://support.microsoft.com/en-in/kb/2879704

http://www.codeproject.com/Questions/459265/Timeout-expired-SQL-Execution-error

http://dba.stackexchange.com/questions/80165/tracking-down-application-timeout-errors-in-sql-server

Protected: Top references for SQL Server DBA

This content is password protected. To view it please enter your password below:

Password: