SQL Server uses cursors to deal with the extremely competitive performance of Tempdb-DBA issues-required by programmers, tempdb-dba

SQL Server uses cursors to deal with the extremely competitive performance of Tempdb-DBA issues-required by programmers, tempdb-dba

Competition in SQL Server tempdb allocation is a common issue for DBAs. Almost all DBAs now know how to create several more files to solve/alleviate the problem. however, deep competition is inevitable. this section analyzes the features of the cursor in tempdb so that it can replace the temporary table/table variable objects in certain scenarios to solve the in-depth Competition in tempdb.

Before throwing out this inevitable problem, let's take a brief look at what is tempdb competition.

We use SQL Server to create a temporary table.

1. Create Table entries in the system table (on the system data page)

2. Allocate an IAM page and find a mixed zone marked in the PFS page

3. Allocate a data page (view the SGAM page, view the PFS page, and update the IAM page)

4. Table records are recorded in the system table

From the above process, we can see that a simple temporary table needs to be searched and updated to a series of system tables/system data pages, and the above operations are reversed when the temporary table is deleted. once the index is created/destroyed in a large amount of concurrency, internal competition will arise. although the cache policy of tempdb can alleviate the IAM, data page allocation, SQL Server tempdb principle-Cache Mechanism parsing practices, the competition remains.

We can see that SGAM, PFS, and other system pages are the only way to create a table, and their allocation competition is very obvious. this is also why the use of multiple data files to distribute system pages (including system tables) in multiple data files to reduce the distribution competition pressure.

At this point, everyone may have guessed what the ultimate problem is, that is, the operation on system objects. Even SQL Server Daniel Paul Randal is a headache.

What are the specific objects? We can test and capture the objects 1-1.

Capture with SQLQUERYSTRESS

Code

Create table # t (id int, str1 varchar (10) --- enable session capture in ssms select resource_description, * from sys. dm_ OS _waiting_tasksWHERE session_id> 50

Figure 1-1

We can see that the typical Pagelatch competition occurs in the system page of tempdb at. We use dbcc page to see the situation on the next page 2-2.

Code

dbcc traceon(3604)godbcc page(2,1,53,1)select OBJECT_NAME(7)----the object_id from dbcc page

Figure 2-2

We can see that there is competition in the System Object sysallocunits. Of course, there are many other system objects that interest you to capture.

A large number of operations on System Object tables make it difficult to further improve the throughput of tempdb. This is caused by the system's own operation mode. Of course, in the face of such a huge volume of tempdb operations, is there no other way? At this time, I cannot give a positive answer, but I can give you a popular It answer: It depends :)

Before introducing the cursor, let's briefly describe the conventional processing method for system table competition in the tempdb competition.

1. Reduce the transaction size of the System Object (for example, select *)

2. Reduce the usage frequency of tempdb (seemingly nonsense, but it may not be used in reality)

3. Less constraints on temporary objects cause extra burden on system objects.

Now let's talk about the cursor. It seems that the cursor cannot be reached. In fact, we just use the cursor feature to solve the corresponding problems in extremely special scenarios.

As you may have guessed, the cursor uses tempdb and is classified into worktables. The objects using worktables include cursor, dbcc checkdb, merge join, and exchange spill. worktables is a common and special method of use in tempdb. It is applied only within SQL Server and defined as "temporary rowsets". Its object id is negative, no system table record is required!

Let's simply verify the description.

Code

Use tempdbcheckpoint --- use dbcc checkdb (master) with caution in the production environment-Here dbcc checkdb is used to explore worktablesselect Description, * from fn_dblog (null, null)

The resulting tempdb Log 2-1

Figure 2-1

We use dbcc page to analyze this page and we can see that this is an IAM page 2-2.

Code

dbcc traceon(3604)dbcc page(2,4,104,3)

Figure 2-2

We further analyzed the data page allocated by IAM and found that it is a simple data page that does not belong to any system object 2-3.

Code

dbcc traceon(3604)dbcc page(2,5,104,3)

Figure 2-3

OK. Now we think that the cursor is also applicable to worktables, and we may think that some cases of the cursor can actually help tempdb ease competition. What are the scenarios? It depends, let's think about It by yourself, but can I use tempdb in the face of competition? Make your own decisions.

Finally, let's see Figure 2-4.

Code

--cursordeclare @cur cursor set @cur =cursor For select * from tt--temp tablecreate table #tt (id int)insert into #tt select * from tt

 

Figure 2-4

Does the above statement change your opinion on the cursor? Programmers, when DBAs tell you to use tempdb too much, do you consider using tempdb in another way? DBAs do not tell programmers to use tempdb too much.

Conclusion The pleasant operation of any system is based on a certain state of balance. We need to find a balance between performance bottlenecks, resource consumption, and corresponding time in a complex environment. What is the balance? It depends :)

Ps: syntax error near SQL server database''

When I was doing a project yesterday, I encountered a title problem. The Code tracking failed to copy the SQL statement in the database, and then re-wrote the same one, and assigned the values to the code, or the same error, that is, I don't know where an error occurs. Finally, I wrote the SQL statement as the simplest select * from tab, or the same error.

Then, no more.

Finally, write the same statement in this statement, and finally find the problem. The new SQL statement's select changes color, and the previously assigned select statements are the same color as the field table name, it proves that the system does not recognize it as a keyword. It deletes the select statement and rewrites it at this position. It is still the same error. In the end, it is found that there is a space in front of this select statement, the space in the full-angle cannot be seen clearly. I suddenly realized that there was a syntax error near '', which means that there was a syntax error in the space, proving that it was not the space format supported by SQL server.

This problem has not been solved by Baidu. I hope it can help others, but it is not particularly difficult, but it is a waste of time to find the problem.