SQL Server FAQs and Solutions

SQL Server FAQs and Solutions

Preface

In QQ groups, groups, and forums, I often help my friends who use SQL Server databases to solve problems. However, some of the most common and basic questions are asked every day, I don't want to answer any more questions. Simply sort out these questions and ask someone to directly send a link.

The problem may be incomplete and will be updated later.

Basic Problem Collection Resource download

Description: Where can I download the XX database operating system?

A: http://www.itellyou.cn/There are a lot of things inside, interested in yourself to see it

Connection problems

Description: The database cannot be connected.

A: Check whether the SQL service is started, whether the user password is correct, the name of the connected instance, and whether the port is correct.

Log Problems

Description: How do I contract a system log when the LDF is full or the log file is very large?

A: In simple recovery mode, SQL Server automatically truncates log files. In full mode, log backup is required.

View recovery mode

Log backup method

Shrink log

Long QUERY \ slow Query

Description: data cannot be found for a long time. It is very slow!

A: In this case, the query statement is blocked by other statements. Add select * from table with (nolock) to the query, which indicates blocking.

You can use sp_who2 or sys. dm_exec_requests view to query specific blocking conditions.

Specific script (view statement running status)

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 sessUNION 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_planFROM 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 qtWHERE 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

Partition Table Problems

Description: tens of millions of data records. Use partitioned tables to improve performance.

A: The Use scenario of Partitioned Tables is mainly to manage data, while improving performance mainly depends on IO parallel processing. Therefore, multiple physical disks need to be properly planned, in most scenarios, you only need to add the correct index for a single query of tens of millions of data.

High Availability options

A: The high availability or read/write splitting technologies provided by SQL mainly include: failover cluster, publish and subscribe, image, log shipper, and AlwaysON available groups (For details, refer to the documents on advanced issues)

Generally, read/write splitting needs to be selected based on different scenarios and requirements, such as the real-time synchronization and the requirements of the read/write splitting function.

It mainly lists several advantages and disadvantages:

Failover cluster: master-slave mode, single-active (the secondary machine is not readable), and hardware resources are wasted. The main scenario is the high availability of databases.

Publishing and subscription: a common method for read/write splitting, flexible configuration, multiple replica nodes, and the ability to publish and subscribe to part of the data (that is, data filtering can be performed), and multiple publishing and subscription modes are provided. Disadvantages: maintenance is troublesome and generally cannot be used for high availability.

Image: the master-slave mode, single-active (the secondary machine is not readable), and hardware resources are wasted. The main scenario is the high availability of databases. Compared with the Failover cluster image, it is highly available at the database level. In an image, you can use snapshots to separate read/write data.

Log transmission: it is mainly used for disaster recovery and can be read on the backup machine. However, the disadvantage is that logs cannot be read during restoration and cannot be restored during reading.

AlwaysON availability group: a comprehensive solution that meets high availability, read/write splitting, and other requirements. Requirements: SQL Server or later

Third-party products: moebius Server Load balancer cluster for dual-active, read load balancing, read/write splitting, etc. Disadvantages: Real-time synchronization is not suitable for large-scale writing systems similar to the collection system.

Service cannot be started

A: The service cannot be started for many reasons. You need to locate the specific problem. If you encounter such a problem, you must first check the log to locate the problem. The log consists of the SQL startup log and windows Log, the following are two articles about how to start a classic parsing SQL statement:

What you don't know about the SQL Server database Startup Process (difficult and difficult for the user database loading process)

What you don't know about the SQL Server database startup process, as well as the analysis and solutions to various problems that cannot be started

Database Design and Table Design Problems

Most of these questions cannot be answered in the QQ group. Many business scenarios cannot be clearly described in a few words.

SQL statement Problems

Description: it is slow to add or remove one SQL statement.

A: The running of SQL statements is very subtle. You need to understand the execution plan. A few words or a picture cannot be solved. The habit of some statements needs to be developed. See:

SQL SERVER Comprehensive Optimization ------- writing good statements is a habit

SQL SERVER Comprehensive Optimization ------- how important is indexing?

AlwaysOn configuration problems

For more information about AlwaysOn configuration, see the following detailed articles by Hua Zi:

Set up SQL Server AlwaysOn from 0 (configure domain control)

Build SQL Server AlwaysOn from 0 (configure a Failover cluster)

Build SQL Server AlwaysOn from 0 (configure AlwaysOn)

Build SQL Server AlwaysOn from 0 (configure remote data center nodes)

2016 AlwaysOn Construction: SQL SERVER 2016 AlwaysOn serverless cluster + SERVER Load balancer construction and Test

AlwaysOn creates a user

First, you must understand that AlwaysOn is in the available group:

1. Only the master node can be written, 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 logon name is created on the master node and the database permission is selected, the database has the permission to create a user but no logon name because of data synchronization.

4. the login name cannot be created on the secondary node in the same way. This is a problem of "user isolation ".

Solution:

1. Add the "Login Name" directly on the master node, for example, create a login name KK

2. Select database permissions and user ing

3. query the script for creating the "Login Name" just now (This script can also be used to synchronize login names after upgrading or restoring the Migration Database)

CREATE PROCEDURE #sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUTAS 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 = @charvalueGODECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @PWD_varbinary varbinary (256)DECLARE @PWD_string varchar (514)DECLARE @Principal_id intDECLARE @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 sysnameDECLARE @language sysnameDECLARE @rolename sysnameDECLARE 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_cursFETCH NEXT FROM login_curs INTO @Principal_id, @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @language, @hasaccess, @denyloginIF (@@fetch_status = -1)BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURNENDSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''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, @denyloginENDCLOSE login_cursDEALLOCATE login_cursGODROP PROCEDURE #sp_hexadecimalGO

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

For more information, see the original article.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.