Common Questions and Solutions Sharing of SQL Server

Time:2019-10-9

Write in front

In QQ group, Wechat group and forum, we often help friends who use the SQL Server database to solve problems, but there are some most common and basic questions that people ask every day, and they don’t want to answer any more, so they sort out these questions, and then some people ask direct links.

Writing this article on a whim may not be comprehensive and will be updated in the future.

Fundamental Issues Collection Resources Download

Description: Where is the XX version of the database operating system downloaded?

Answer: There are many things in http://www.itellyou.cn/. Look for yourself if you are interested.

Connection problem

Description: The database is not connected

Answer: Please confirm whether the SQL service is started, whether the user password is correct, the instance name of the connection, and whether the port is correct.

Logging issues

Description: How does the system log LDF shrink when it is full or the log file is very large?

Answer: SQL Server will automatically truncate log files in simple recovery mode, and log backup is required in full recovery mode.

Recovery Mode View

The Way of Log Backup

Contraction log

Query for a long time very slow

Description: Queries for a long time can not find the data, very slow!

Answer: In this case, query statements are usually blocked by other statements. Add select * from table with (nolock) to the query if it can be found to illustrate the blocking

Specific blocking situations can be queried using the sp_who2 or sys.dm_exec_requests view

Specific scripts (see statement run)


WITH sess AS
(
 SELECT
 es.session_id,
 database_name = DB_NAME(er.database_id),
 er.cpu_time,
 er.reads,
 er.writes,
 er.logical_reads,
 login_name,
 er.status,
 blocking_session_id,
 wait_type,
 wait_resource,
 wait_time,
 individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),
 parent_query = qt.text,
 program_name,
 host_name,
 nt_domain,
 start_time,
 DATEDIFF(MS,er.start_time,GETDATE()) as duration,
 (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan
 FROM
 sys.dm_exec_requests er
 INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
 CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
 WHERE
 es.session_id > 50
 AND es.session_Id NOT IN (@@SPID)
)
SELECT
 *
FROM
 sess
UNION ALL SELECT
 es.session_id,
 database_name = '',
 0,
 0,
 0,
 0,
 login_name,
 es.status,
 0,
 '',
 '',
 '',
 qt.text,
 parent_query = qt.text,
 program_name,
 host_name,
 nt_domain,
 es.last_request_start_time,
 DATEDIFF(MS,es.last_request_start_time,GETDATE()) as duration,
 NULL AS query_plan
FROM
 sys.dm_exec_sessions es
 INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
 CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
WHERE
 ec.most_recent_session_id IN
 (
 SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)
 )
ORDER BY
 1, 2

Subarea table problem

Description: Ten million levels of data use partition tables to improve performance

Answer: The use scenarios of partition tables are mainly to manage data, while the performance improvement is mainly based on IO parallel, which requires rational planning of multiple physical disks. In most scenarios, tens of millions of data only need to add the correct index for single mode query.

High Availability Options

Answer: The main high availability or read-write separation technologies included in SQL are: failover clustering, publish-subscribe, mirror, log transfer, AlwaysON Available Group (which can be viewed in detail in the information of advanced questions).

In general, the choice of read-write separation needs to be based on different scenarios and requirements, such as real-time synchronization, the need for read-write separation function.

Several advantages and disadvantages are listed.

Fault Transfer Cluster: main and standby mode, single activity (auxiliary machine is not readable), waste of hardware resources, the main scenario is the high availability of the database.

Publishing and subscribing: Read-write separation is a common way, flexible configuration, multiple replica nodes, can publish subscription data (that is, data filtering), and provide a variety of publishing and subscribing modes. Disadvantage: maintenance is troublesome, generally can not be used as high availability.

Mirror: Main and standby mode, single activity (auxiliary machine is not readable), waste of hardware resources, the main scenario is the high availability of the database. Compared to failover cluster mirroring, it is highly available at the database level. In the mirror, we can use snapshots to realize the separation of reading and writing.

Log transmission: Mainly used for disaster preparedness, readable on standby machine, but the disadvantage is that the log can not be read when it is restored, and can not be restored when it is read.

AlwaysON Available Group: Comprehensive solution to meet the needs of high availability, read-write separation, etc. Requirements: SQL Server Version 2012 or above

Third-party products: Moebius load balancing cluster, dual-activity, read load balancing, read-write separation and so on. The disadvantage of real-time synchronization is not suitable for large-scale writing systems similar to acquisition systems.

Service could not start

Answer: There are many reasons why the service can’t start. It needs specific positioning. If you encounter such problems, you should first look at the log positioning problem. There are two main parts of the log, the SQL start log and the Windows log. Here are two classic articles on parsing the SQL start.

What you do not know about the SQL Server database startup process (user database loading process complications)

What you don’t know about the start-up process of SQL Server database and the analysis and solution techniques of various problems that can’t be started

Database design, table design

Most of these questions can not be answered at all in QQ group. Many business scenarios can not be described clearly in a few sentences.

Question of SQL Statement

Description: It becomes slow to add or subtract a condition from a SQL statement

Answer: The operation of SQL statements changes very subtly. You need to understand the execution plan. A few sentences or a map can not solve the problem. Some sentences need to develop the habit. See:

Overall optimization of SQL SERVER – — Writing good sentences is a habit

Overall optimization of SQL SERVER – How important is index?

AlwaysOn Configuration Problem

For AlwaysOn configuration, see some very detailed articles by Huazi:

Starting from 0, build the first article of SQL Server Always On (configuring domain control)

Starting from 0 to build the second article of SQL Server Always On (Configuring Fault Transfer Cluster)

Starting from 0, build the third article of SQL Server Always On (Configuring Always On)

Starting from 0 to build the fourth article of SQL Server Always On (Configuration of remote computer room nodes)

AlwaysOn Building in 2016: SQL SERVER 2016 AlwaysOn Domain-free Cluster + Load Balancing Building and Simple Testing

AlwaysOn New Users

First of all, understand that in the AlwaysOn Available Group:

1. Only the primary node is writable and the secondary node is read-only.

2. Permissions are divided into two parts: instance-level “login name” and database-level “user”

3. After the primary node creates the login name and chooses the database permission, because of data synchronization, the database permission of the newly created user has been established from the library, but there is no login name.

4. You can’t create a login name in the same way as a secondary node, which is a “user isolation” problem.

Resolvent:

1. Add a “login name” directly to the primary node, such as creating a login name KK.

2. Selecting database permissions and user mapping

3. Query the script that just created the “login name” (this script can also be used to upgrade or migrate database restore, login name synchronization issues)


CREATE PROCEDURE #sp_hexadecimal
 @binvalue varbinary(256),
 @hexvalue varchar (514) OUTPUT
AS
 DECLARE @charvalue varchar (514)
 DECLARE @i int
 DECLARE @length int
 DECLARE @hexstring char(16)

 SELECT @charvalue = '0x'
 SELECT @i = 1
 SELECT @length = DATALENGTH (@binvalue)
 SELECT @hexstring = '0123456789ABCDEF'
 WHILE (@i <= @length)
 BEGIN
 DECLARE @tempint int
 DECLARE @firstint int
 DECLARE @secondint int
 SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 SELECT @firstint = FLOOR(@tempint/16)
 SELECT @secondint = @tempint - (@firstint*16)
 SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)
 SELECT @i = @i + 1
 END
 SELECT @hexvalue = @charvalue
GO

DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @Principal_id int
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
DECLARE @language sysname
DECLARE @rolename sysname
DECLARE login_curs CURSOR FOR SELECT 
 p.principal_id,
 p.sid, 
 p.name, 
 p.type, 
 p.is_disabled, 
 p.default_database_name, 
 p.default_language_name,
 l.hasaccess, 
 l.denylogin 
FROM 
 sys.server_principals p 
LEFT JOIN 
 sys.syslogins l ON ( l.name = p.name ) 
WHERE 
 p.type IN ( 'S', 'G', 'U' ) AND 
 p.name <> 'sa'

OPEN login_curs

FETCH NEXT FROM login_curs INTO @Principal_id, @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @language, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
 PRINT 'No login(s) found.'
 CLOSE login_curs
 DEALLOCATE login_curs
 RETURN
END
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 PRINT ''
 SET @tmpstr = '-- Login: ' + @name
 PRINT @tmpstr
 IF (@type IN ( 'G', 'U'))
 BEGIN -- NT authenticated account/group
  SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + '], DEFAULT_LANGUAGE = [' + @language + ']'
 END
 ELSE 
 BEGIN -- SQL Server authentication
  -- obtain password and sid
  SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
  EXEC #sp_hexadecimal @PWD_varbinary, @PWD_string OUT
  EXEC #sp_hexadecimal @SID_varbinary,@SID_string OUT

  -- obtain password policy state
  SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
  SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

  SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + '], DEFAULT_LANGUAGE = [' + @language + ']'

  IF ( @is_policy_checked IS NOT NULL )
  BEGIN
  SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
  END
  IF ( @is_expiration_checked IS NOT NULL )
  BEGIN
  SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
  END
 END
 IF (@denylogin = 1)
 BEGIN -- login is denied access
  SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
 END
 ELSE IF (@hasaccess = 0)
 BEGIN -- login exists but does not have access
  SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
 END
 IF (@is_disabled = 1)
 BEGIN -- login is disabled
  SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
 END
 PRINT @tmpstr
 PRINT 'GO'
 DECLARE server_role_members_curs CURSOR FOR 
  SELECT 
  (SELECT [name] FROM sys.server_principals WHERE principal_id = role_principal_id) AS rolename
  FROM 
  sys.server_role_members 
  WHERE 
  member_principal_id = @Principal_id
 OPEN server_role_members_curs

 FETCH NEXT FROM server_role_members_curs INTO @rolename
 WHILE (@@fetch_status <> -1)
 BEGIN
  SELECT @tmpstr = 'EXEC master..sp_addsrvrolemember @loginame = N''' + @name + ''', @rolename = N''' + @rolename + ''''
  PRINT @tmpstr
  PRINT 'GO'
  FETCH NEXT FROM server_role_members_curs INTO @rolename
 END
 CLOSE server_role_members_curs
 DEALLOCATE server_role_members_curs 
 END
 FETCH NEXT FROM login_curs INTO @Principal_id, @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @language, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
GO

DROP PROCEDURE #sp_hexadecimal
GO

4. Find the query script and run it on the auxiliary node (SID is the main one)

For further questions, please click on the original text to read.

The above is the whole content of this article. I hope it will be helpful to everyone’s study, and I hope you will support developpaer more.