For a slow query, we usually need to know which steps are expensive first, and then try some improvement methods. In general, if you cannot solve the problem by improving the hardware performance or adjusting the OS or SQL server settings, the remaining options are usually the following:1. Add the index of the corresponding field for the "scan" operation.2. Somet
Server. System operations are the same as if each member server has a copy of the original table, but in fact each server has only one member table and a distributed partition view. The data location is transparent to the application.
11. Rebuild the index DBCC reindex, DBCC indexdefrag, shrink data and log DBCC shrinkdb, and DBCC shrinkfile. set automatic log
Get Server name:
Select SERVERPROPERTY (' machinename ')
SELECT @ @SERVERNAME
Select HOST_NAME ()
To obtain an IP address, you can use xp_cmdshell to execute the ipconfig command:
However, in many cases, because the security issue is not allowed to use xp_cmdshell, you can query sys.dm_exec_connections:
SELECT SERVERNAME = CONVERT (NVARCHAR (128), serverproperty (' SERVER
;--current session process ID
SELECT @ @textSize;
SELECT @ @version;--Current database version information
9. System Statistic function
SELECT @ @CONNECTIONS;--Number of connections
SELECT @ @PACK_RECEIVED;
SELECT @ @CPU_BUSY;
SELECT @ @PACK_SENT;
SELECT @ @TIMETICKS;
SELECT @ @IDLE;
SELECT @ @TOTAL_ERRORS;
SELECT @ @IO_BUSY;
SELECT @ @TOTAL_READ;--Read disk count
SELECT @ @PACKET_ERRORS;--Number of network packet errors that occurred
SELECT @ @TOTAL_WRITE;--sqlserver the number of disk writes p
execute the stored procedure. 1 -- Execute the stored procedure.
2 -- This generates the execution plan with a Key Lookup (Clustered).
3 EXEC 80000
4 GO Now when you look at the actual execution plan, you will see that the query optimizer has selected a clustered index scan operator with 419 logical reads. SQL Server
partition table must be created.
B. After creating a member table, define a distributed partition view on each Member Server, and each view has the same name. In this way, queries that reference the view name of a distributed partition can run on any Member Server. System operations are the same as if each member server has a copy of the original table, but in f
find books, we generally use the way (the definition of parameters and data collection on the Assignment simulation page): 1 DECLARE @publishers VARCHAR (a);
2 SET @publishers = ' Tsinghua University Press ' ;
3 SELECT * from WHERE Publishers=@publishers The results are as follows: When the publisher filter condition is NULL, how do you write SQL? 1 DECLARE @publishers VARCHAR (a);
2 SELECT * from WHERE Publishers=ISNULL(@publishers, Publishers
the Name of the current Language.
Select @ lock_timeout; -- returns the current lock timeout setting for the current session (MS)
Select @ max_connections; -- returns the maximum number of user connections allowed by the SQL Server instance at the same time.
Select @ MAX_PRECISION AS 'max Precision '; -- returns the Precision level used by the decimal and numeric data types.
Select @ SERVERNAME; -- Name of
Source: http://www.cnblogs.com/doosmile/archive/2012/03/16/2400646.html--Connect remote SQL or insert data with OPENROWSET--if it's just temporary access, you can use OPENROWSET directly--query ExampleSELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password, database name. dbo. Table name)
If you want to query Index Server through SQL Server, you must use the OPENQUERY function. The syntax structure is as follows.
OPENQUERY (pai_server, 'query ')
The linked_server parameter indicates the connection name. The query p
Introduction
In SQL Server, each query finds the shortest path to achieve its goal. If the database only accepts one connection, only one query is executed at a time. Therefore, queries must be completed quickly and easily. However, most databases need to process multiple queries at the same time. These queries will n
multiple execution plans. Some parameters of the memory grant estimate are stored in the compilation plan, and it itself has a mechanism for calculating the amount of memory that needs to be granted to the query when it is actually executed. Memory User (consumers) A successfully executed query consists of three primary memory users: compilation, caching, and memory grant. Compiling: Creating and in hundr
Many people don't know how SQL statements are executed in SQL Server, and they worry that the SQL statements they write will be misunderstood by SQL Server. Like what:SELECT * FROM table1 where name= ' Zhangsan ' and TID > 10000an
Enable AD Hoc distributed Queries: ' Show advanced Options ', 1
Reconfigure
' Ad Hoc distributed Queries ', 1
Reconfigure
When you are finished using, close ad Hoc distributed Queries:' Ad Hoc distributed Queries ', 0
Reconfigure
' Show advanced Options ', 0
Reconfigure
Operation:
' SQLOLEDB ' ' SQL Server name ' ' User name ' ' Password '
' SQLOLEDB ' ' S
Tags: index solution based on keyword har results in SQL Server failure efficiencyProblem: There's a lot of data on both tables. The A1 field in a table needs to be associated with the B table primary key query A1 field stores multiple B table primary keys The format is: Format 1:B1,B2,B3 Format 2:B4 Format 3:b5,b6 Comma-delimited minority This leads to the use o
searching in indexed character data using non-heading letters. This also makes the engine unusable with indexes.See the following example:SELECT * from T1 WHERE NAME like '%l% 'SELECT * from T1 WHERE substing (name,2,1) = ' L 'SELECT * from T1 WHERE NAME like ' l% 'Even if the NAME field is indexed, the first two queries are still unable to take advantage of the index completion to speed up the operation, the engine has toTable all data-by-operation to complete the task. The third
Label: CREATE VIEW View_1--Create a new view name as--functions for building viewsSelectStudent.sno,sname,cno,degree fromStudent join score on student.sno=Score.sno GoSelect* fromView_1wheresno='1'
Select* from
(SelectStudent.sno,sname,cno,degree fromStudent join score on STUDENT.SNO=SCORE.SNO) asTable2 wheresno='101'--define as a temporary table with AS and then query the results from the table.
is also a sub-qu
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.