SQL Server uses cursors to handle the tempdb competition-DBA problem-programmers must know _mssql

SQL Server tempdb allocation Competition is a problem for DBAs, and almost all DBAs now know how to build several more files to solve Mitigate the problem. But deep competition is still inevitable. Here's to you. The characteristics of downstream mark in tempdb make it replace the temporary table/table variable object in a certain scene to solve the deep problem of tempdb competition.

Before throwing this unavoidable question, let's briefly look at what is the tempdb competition.

We use SQL Server to create a temporary table to describe the process

1 Creating a table entry in the system table (in the System data page)

2 Assign an IAM page and find a mixed area marked in the PFS page

3 Assign a data page (view Sgam page, view PFS page and update, update IAM page)

4 table records are recorded in the system table

From the above process you can see that creating a simple temporary table requires looking up, updating a series of system table/system data pages, and doing the reverse when you are done using the Delete temporary table. Index corresponding creation/destruction once a large number of concurrent, Internal competition has also emerged. Although Tempdb's caching strategy can mitigate the IAM, data page allocation, SQL Server tempdb principle-caching mechanism resolution practices in the corresponding creation process, the competition remains.

You can see that the SGAM,PFS system page is the only way to create the table, and his distribution competition is very obvious. This is why multiple data files are used to allow system pages (including system tables) to be dispersed across multiple data files to mitigate the stress caused by the allocation of competition.

Perhaps everyone has guessed what the ultimate problem is, the operation of the system object. Even the SQL Server Daniel Paul Randal has a headache.

Specifically which objects, we can simply test the capture below as shown in Figure 1-1

Using sqlquerystress capture

Code

CREATE TABLE #t
(id int,
str1 varchar)
---Open session in SSMs capture
SELECT resource_description,* from Sys.dm_os_waiting_tasks
WHERE session_id>50

Figure 1-1

You can see the typical Pagelatch competition in the system page 2:1:53 in tempdb in the diagram. We use the DBCC page to look at the next page as shown in Figure 2-2

Code

DBCC TRACEON (3604)
go
DBCC page (2,1,53,1)
select object_name (7)----The OBJECT_ID from DBCC page

Figure 2-2

You can see the competition in the system object Sysallocunits, of course there are many other system objects, interested friends to capture themselves.

The large number of operations on the System Object table makes tempdb's throughput difficult to further upgrade, which is caused by the system itself, of course, in the face of such a huge amount of tempdb use, there is no other way? At this time I can not give a positive answer, but can give you a popular answer to the IT industry: I T depends:)

Before introducing a cursor, let's talk about the general approach to competition for system tables in the tempdb competition

1 reduce transaction size for system objects (for example, select * into #的使用)

2 Reduce the usage frequency of tempdb (seemingly nonsense, but actually it may not be used so much)

The use of constraints in 3 temporary objects creates additional system object burdens.

Okay, so here's the cursor, the seemingly Bishi thing, actually, we're just using the cursor's characteristics to solve the problem in a very special scenario.

As you may have guessed, cursors are used in tempdb, categorized into worktables, using worktables objects such as cursors, DBCC CHECKDB,MERGE Join,exchange Spill and so on. Worktables is a common and special use in tempdb, which he applies only within SQL Server, defines it as "temporary rowsets", and his object ID is negative and does not require a system table record!

Let's simply verify the instructions below.

Code

Use tempdb
checkpoint---the production environment with the caution of
DBCC CHECKDB (MASTER) – Use DBCC CHECKDB here to explore worktables
Select description,* From Fn_dblog (null,null)

Get the tempdb log as shown in Figure 2-1

Figure 2-1

We analyze this page with the DBCC page to see that this is an IAM page as shown in Figure 2-2

Code

DBCC TRACEON (3604)
DBCC page (2,4,104,3)

Figure 2-2

We then analyze the IAM-assigned data page and find that he is a simple data page that does not belong to any system object as shown in Figure 2-3

Code

DBCC TRACEON (3604)
DBCC page (2,5,104,3)

Figure 2-3

OK, so this reminds us that cursors are also applicable to worktables, and we may associate some cursors with a scenario that can actually help tempdb mitigate competition. What kind of scene? It depends, everyone to Lenovo it, but the corresponding competition in tempdb I can use? Friends make their own choices.

Finally look at the picture and talk like Figure 2-4

Code

--cursor
declare @cur cursor 
set @cur =cursor for SELECT * from TT
--temp table
CREATE TABLE #tt (ID int) C5/>insert into #tt select * from TT

Figure 2-4

Does the description change your view of the cursor? Programmers friends, when the DBA tells you to use tempdb too much, consider using tempdb differently, DBA friends, and don't easily tell programmers to overuse tempdb.

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

Syntax error near Ps:sql server database

When I was doing the project yesterday, encountered a problem with the title, code tracking to copy the SQL statement in the database can not execute, and then rewrite the same, and then in the assignment to the code, or the same error, is not know where the error, and finally the SQL statement written as the simplest select * from tab It's the same mistake.

Then, then it won't be.

And finally write the same statement in this statement, finally found the problem, the new write SQL statement Select Color, and the previous assignment of the Select and field table name color, proof that the system does not recognize it is a keyword, delete this select in this position to write again, or the same error, Finally found that the original in front of this select has a full corner of the space, the full-width space is really invisible with the naked eye ah, suddenly, just know that "there are grammatical errors, meaning that the space has syntax errors, proof is not SQL Server support space format."

This problem Baidu, also did not solve, hope can help other people, is not particularly difficult things, but find the problem is still a waste of time.