Put a question to bosses-CodePudding

Say first background, our unit performance server and switches are ok, internal LAN applications, on a MES system and an ERP system, ERP line speed also is the friend, the database is Microsoft sqlsever2008, operating system is Windows, but!!!!!! MES system query speed is too slow, both the client and the IE browser side is particularly slow, IE browser side is the kernel,
Problem is that we can work it out a little faster, the browser client and sure to change an item, but any optimization method for the server side,

CodePudding user response:

Is all query MES system is slow, or a certain function of query slowly, slowly, if it is all an estimated server hardware upgrade, if it is some slow, that is better targeted to optimize (of course, if at this time to upgrade hardware, estimate can improve).

CodePudding user response:

Performance is also done on the basis of, confirm the amount of data, find out, for example, under the database to see execution plan, confirm the slow in what place

CodePudding user response:

Tell me first:
1, the server's configuration
2, the size of the main table (how many data quantity)
3, execute the following SQL, look at the actual slow SQL:

 SELECT TOP 10 OBJECT_NAME (qt objectid, qt. DbId) AS procName, 
DB_NAME (qt. DbId) AS [DB_NAME], 
Qt. The text AS SQL_Full, 
The SUBSTRING (
Qt. Text, 
(qs. Statement_start_offset/2) + 1, 
(
(
CASE statement_end_offset 
THEN the WHEN - 1 DATALENGTH (qt. Text) 
The ELSE qs. Statement_end_offset 
END 
- qs. Statement_start_offset 
)/2 
) + 1 
) AS SQL_Part - statistics corresponding part of the statement 
, 
Qs creation_time, 
Qs last_execution_time, 
Qs execution_count, 
Qs last_elapsed_time/1000000 AS lastElapsedSeconds, 
Qs last_worker_time/1000000 AS lastCpuSeconds, 
CAST (
Qs. Total_elapsed_time/1000000.0/(
CASE 
The WHEN qs. Execution_count=0 THEN - 1 
The ELSE qs. Execution_count 
END 
) AS a DECIMAL (28, 2) 
) AS avgDurationSeconds, 
CAST (qs) last_logical_reads AS BIGINT) * 1.0/(1024 * 1024 * 8060 AS 
LastLogicReadsMB, 
Qs last_logical_reads, 
Qs. Plan_handle 
The FROM sys. Dm_exec_query_stats qs 
CROSS the APPLY sys. Dm_exec_sql_text (qs) sql_handle) AS qt 
CROSS the APPLY sys. Dm_exec_query_plan (qs) plan_handle) AS p 
WHERE qs. Last_execution_time & gt;=the CONVERT (CHAR (10), GETDATE (), 120) + '08:00' - after 8 PM today slow SQL 
AND qs. Last_elapsed_time & gt;=3 * 1000 * 1000 - only to take the record of the execution time is more than 3 seconds 
AND qt. [text] NOT LIKE '% Proc_DBA %' 
The ORDER BY 
Qs. Last_worker_time DESC