Низкая производительность в SQL 2014 в SELECT без предложения WHERE

#performance #sql-server-2014

#Производительность #sql-server-2014

Вопрос:

Мы перенесли SQL Server STANDARD 2008 (уровень совместимости 2005) на SQL Server DEVELOPER 2014 (уровень совместимости 2008), и мы заметили низкую производительность в 2014

ВЕРСИЯ SQL SERVER 2014 Microsoft SQL Server 2014 (SP3) (KB4022619) — 12.0.6024.0 (X64) 7 сентября 2018 01:37:51 Авторское право (c) Microsoft Corporation Developer Edition (64-разрядная версия) на Windows NT 6.3 (сборка 9600: ) (Гипервизор)

ВЕРСИЯ SQL SERVER 2008 Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) — 10.50.6560.0 (X64) 28 декабря 2017 15:03:48 Авторское право (c) Microsoft Corporation Standard Edition (64-разрядная версия) на Windows NT 6.1 (сборка 7601: пакет обновления 1)

Мы читали много сообщений о планах выполнения, индексах и новой статистике SQL 2014, но в нашем случае один «SELECT * FROM table» без предложения WHERE занимает вдвое больше времени в SQL 2014.

Эти предложения SQL были запущены в консоли управления SQL Server

Есть какие-нибудь подсказки?

Мы прикрепляем таблицу времени сравнения

Новое время с использованием SELECT * в #temp ИЗ таблицы

ИСПОЛЬЗОВАНИЕ ПАМЯТИ SQL SERVER 2014

ИСПОЛЬЗОВАНИЕ ПАМЯТИ SQL SERVER 2008

КАРТА оперативной памяти

Память, о которой сообщает консоль SQL

запустите spconfigure

Сценарий памяти

1. Я думаю, вам следует добавить, где запущены консоль и сервер

2. Спасибо @malhal. Консоль работает на том же сервере SQL Server. Оба сервера имеют одинаковую конфигурацию, количество процессоров и памяти. Оба являются виртуальными серверами

3. Я обновил свой ответ, поскольку, похоже, мы нашли причину медлительности этого компьютера. Оставшейся оперативной памяти недостаточно как для сервера, так и для консоли, для сервера вы можете исправить это, предоставив страницы блокировки учетной записи SQL Server в памяти, для клиента только Win Admin может вам помочь или просто запустить его на другом ПК

Ответ №1:

Это может быть другая конфигурация сети или на ваших серверах могут быть разные диски, поэтому вам следует проверить, сколько времени выполняется ваш запрос БЕЗ отправки результата клиенту.

В качестве простейшего теста попробуйте выполнить select в таблице #tmp, например:

 SELECT * into #t FROM table

И сравните время выполнения на обоих серверах. Здесь вы не отправляете результаты клиенту и исключаете проблемы с сетью.

Для более глубокого сравнения производительности ваших серверов при условии, что нагрузка на эти серверы одинакова, пожалуйста, обновите свои вопросы результатами этого запроса: Статистика ожидания SQL Server

Обновить

Из дальнейшего исследования мы имеем:

клиент, работающий на этом компьютере, работает медленнее, чем на старом сервере
новый сервер постоянно считывает данные с диска, PAGEIOLATCH_SH находится на вершине статистики ожидания, а 64% слишком высоки, серверу требуется больше оперативной памяти

Это связано с тем, что новый сервер установлен в виртуальной среде, а его оперативная память не настроена для выделения ad. Виртуальная машина съедает его, так что серверу не хватает памяти, а также клиенту. Лучшее решение здесь — поговорить с администратором Windows и попросить его перенастроить оперативную память для этого сервера.

В случае, если администратор Windows не хочет перенастраивать виртуальную машину, я советую запустить клиент на другом компьютере, чтобы у него была вся необходимая память, и включить опцию блокировки страниц в памяти (Windows)

Когда учетная запись SQL Server имеет страницы блокировки в предоставлении памяти, она займет столько памяти, сколько ей нужно (но <= MaxServerMemory настроено), не освобождая ее для виртуальной машины, когда виртуальная машина запрашивает ее. Таким образом, на вашем сервере будет 28 ГБ, которые были настроены для него, а 4 ГБ осталось для Windows, что идеально.

RAMMAP — это инструмент, который помогает вам увидеть распределение оперативной памяти на вашем сервере. Я прилагаю свою фотографию, на которой виртуальная машина потребляла оперативную память с моего сервера, чтобы вы могли это ясно видеть:

Здесь сервер имеет 64 ГБ оперативной памяти, но SQL Server получает только 36 ГБ, даже если ему нужно больше, потому что виртуальная машина запрашивает ее обратно с сервера. Когда учетная запись SQL Server блокирует страницы в памяти, она игнорирует утверждения виртуальной машины, и только конфигурация MaxServerMemory ограничивает объем оперативной памяти, который она может занимать.

Обновить

Скрипт для распределения оперативной памяти

 DECLARE @OtherClerksTop INT
SET @OtherClerksTop = 5

SET nocount on 
SET TRANSACTIon ISOLATIon LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

DECLARE @sql NVARCHAR(max)
DECLARE @Version VARCHAR(100)
DECLARE @ServiceName NVARCHAR(100)

SET @Version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(100))
SET @ServiceName = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
                            THEN 'SQLServer:'
                        ELSE 'MSSQL$'   @@SERVICENAME   ':'
                        END

DECLARE @Perf TABLE (object_name nvarchar(20), counter_name nvarchar(128), instance_name nvarchar(128), cntr_value BIGINT, formatted_value NUMERIC(20, 2), shortname NVARCHAR(20))
INSERT INTO @Perf(object_name, counter_name, instance_name, cntr_value, formatted_value, shortname)
SELECT 
  CASE 
    WHEN CHARINDEX ('Memory Manager', object_name)> 0 THEN 'Memory Manager'
    WHEN CHARINDEX ('Buffer Manager', object_name)> 0 THEN 'Buffer Manager'
    WHEN CHARINDEX ('Plan Cache', object_name)> 0 THEN 'Plan Cache'
    WHEN CHARINDEX ('Buffer Node', object_name)> 0 THEN 'Buffer Node' -- 2008
    WHEN CHARINDEX ('Memory Node', object_name)> 0 THEN 'Memory Node' -- 2012
    WHEN CHARINDEX ('Cursor', object_name)> 0 THEN 'Cursor'
    WHEN CHARINDEX ('Databases', object_name) > 0 THEN 'Databases'
    ELSE NULL 
  END AS object_name,
  CAST(RTRIM(counter_name) AS NVARCHAR(100)) AS counter_name, 
  RTRIM(instance_name) AS instance_name, 
  cntr_value,
  CAST(NULL AS DECIMAL(20,2)) AS formatted_value,
  SUBSTRING(counter_name,  1, PATINDEX('% %', counter_name)) shortname
FROM sys.dm_os_performance_counters 
WHERE (object_name LIKE @ServiceName   'Buffer Node%'     -- LIKE is faster than =. I have no idea why
    OR object_name LIKE @ServiceName   'Buffer Manager%' 
    OR object_name LIKE @ServiceName   'Memory Node%' 
    OR object_name LIKE @ServiceName   'Plan Cache%')
  AND (counter_name LIKE '%pages %' 
    OR counter_name LIKE '%Node Memory (KB)%'
    OR counter_name = 'Page life expectancy' 
    )
    OR  (object_name = @ServiceName   'Memory Manager'
        AND counter_name IN ('Granted Workspace Memory (KB)', 'Maximum Workspace Memory (KB)',
                                    'Memory Grants Outstanding',     'Memory Grants Pending',
                                    'Target Server Memory (KB)',     'Total Server Memory (KB)',
                                    'Cnecti Memory (KB)',        'Lock Memory (KB)',
                                    'Optimizer Memory (KB)',         'SQL Cache Memory (KB)',
                                    -- for 2012
                                    'Free Memory (KB)',              'Reserved Server Memory (KB)',
                                    'Database Cache Memory (KB)',    'Stolen Server Memory (KB)',
                                    -- XTP
                                    'Log Pool Memory (KB)')
      )
    OR (object_name LIKE @ServiceName   'Cursor Manager by Type%'
      AND counter_name = 'Cursor memory usage'
      AND instance_name = '_Total'

      )
-- Add UNIONt to 'Cursor memory usage'
UPDATE @Perf
SET counter_name = counter_name   ' (KB)'
WHERE counter_name = 'Cursor memory usage' 

-- Cvert values from pages and KB to MB and rename counters accordingly
UPDATE @Perf
SET 
  counter_name = REPLACE(REPLACE(REPLACE(counter_name, ' pages', ''), ' (KB)', ''), ' (MB)', ''), 
  formatted_value = 
  CASE 
    WHEN counter_name LIKE '%pages' THEN cntr_value/128. 
    WHEN counter_name LIKE '%(KB)' THEN cntr_value/1024. 
    ELSE cntr_value
  END

-- Delete some pre 2012 counters for 2012 in order to remove duplicates
DELETE P2008
FROM @Perf P2008
INNER JOIN @Perf P2012  
   on replace(P2008.object_name, 'Buffer', 'Memory') = P2012.object_name AND P2008.shortname = P2012.shortname
WHERE P2008.object_name IN ('Buffer Manager', 'Buffer Node')

-- Update counter/object names so they look like in 2012
UPDATE PC
SET 
  object_name = REPLACE(object_name, 'Buffer', 'Memory'),
  counter_name = ISNULL(M.NewName, counter_name)  
FROM @Perf PC
  LEFT JOIN
  (
    SELECT 'Free' AS OldName, 'Free Memory' AS NewName UNION ALL
    SELECT 'Database', 'Database Cache Memory' UNION ALL
    SELECT 'Stolen', 'Stolen Server Memory' UNION ALL
    SELECT 'Reserved', 'Reserved Server Memory' UNION ALL
    SELECT 'Foreign', 'Foreign Node Memory'
  ) M ON M.OldName = PC.counter_name
  AND NewName NOT IN (SELECT counter_name FROM @Perf WHERE object_name = 'Memory Manager') 
WHERE object_name IN ('Buffer Manager', 'Buffer Node')

-- Add Memory Clerks

-- Add some Memory Clerk descriptis
IF OBJECT_ID('tempdb..#mem_clerks_desc') IS NOT NULL DROP TABLE #mem_clerks_desc
CREATE TABLE #mem_clerks_desc(type varchar(60), descripti varchar(60), is_perf_counter bit)
INSERT #mem_clerks_desc VALUES('CACHESTORE_BROKERTO','Service Broker Transmissi Object Cache', 0)
INSERT #mem_clerks_desc VALUES('CACHESTORE_COLUMNSTOREOBJECTPOOL','Column Store Object Pool', 0)
INSERT #mem_clerks_desc VALUES('CACHESTORE_OBJCP','Object Plans', 1)
INSERT #mem_clerks_desc VALUES('CACHESTORE_PHDR','Bound Trees', 1)
INSERT #mem_clerks_desc VALUES('CACHESTORE_SEHOBTCOLUMNATTRIBUTE','SE Shared Column Metadata Cache', 0)
INSERT #mem_clerks_desc VALUES('CACHESTORE_SQLCP','SQL Plans', 1)
INSERT #mem_clerks_desc VALUES('CACHESTORE_SYSTEMROWSET','System Rowset Store', 0)
INSERT #mem_clerks_desc VALUES('CACHESTORE_TEMPTABLES','Temporary Tables amp; Table Variables', 1)
INSERT #mem_clerks_desc VALUES('CACHESTORE_XPROC','Extended Stored Procedures', 1)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_QUERYDISKSTORE','Query Store Memory', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_QUERYDISKSTORE_HASHMAP','Query Store Hash table ', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SOSMEMMANAGER','SOS Memory Manager', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SOSNODE','SOS Node', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SOSOS','SOS Memory Clerk', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLBUFFERPOOL','Database Cache Memory', 1)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLCLR','SQL CLR', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLCLRASSEMBLY','SQL CLR Assembly', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLCNECTIPOOL','Cnecti Memory', 1)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLGENERAL','SQL General', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLLOGPOOL','Log Pool Memory', 1)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLOPTIMIZER','Optimizer Memory', 1)
-- INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLQERESERVATIS','Granted Workspace Memory (Used Reserved)', 0) -- Exclude completely
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLQUERYEXEC','SQL Query Exec', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLQUERYPLAN','SQL Query Plan', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLSERVICEBROKER','SQL Service Broker', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLSTORENG','SQL Storage Engine', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_SQLTRACE','SQL Trace', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_XE','Extended Events Engine', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_XE_BUFFER','Extended Events Buffer', 0)
INSERT #mem_clerks_desc VALUES('MEMORYCLERK_XTP','In-Memory objects (XTP)', 0)
INSERT #mem_clerks_desc VALUES('OBJECTSTORE_LOCK_MANAGER','Lock Memory', 1)
INSERT #mem_clerks_desc VALUES('OBJECTSTORE_SERVICE_BROKER','Service Broker (Object Store)', 0)
INSERT #mem_clerks_desc VALUES('OBJECTSTORE_SNI_PACKET','SNI Packet (Object Store)', 0)
INSERT #mem_clerks_desc VALUES('OBJECTSTORE_XACT_CACHE','Transactis Cache (Object Store)', 0)
INSERT #mem_clerks_desc VALUES('USERSTORE_DBMETADATA','Database Metadata (User Store)', 0)
INSERT #mem_clerks_desc VALUES('USERSTORE_OBJPERM','Object Permissis (User Store)', 0)
INSERT #mem_clerks_desc VALUES('USERSTORE_SCHEMAMGR','Schema Manager (User Store)', 0)
INSERT #mem_clerks_desc VALUES('USERSTORE_TOKENPERM','Token Permissis (User Store)', 0)

IF OBJECT_ID('tempdb..#mem_clerks') IS NOT NULL DROP TABLE #mem_clerks
CREATE TABLE #mem_clerks(type varchar(60), mem_type varchar(20), pages_mb DECIMAL(20, 2))

IF CAST(SUBSTRING(@VersiON, 1, CHARINDEX('.', @Version, 1)-1) AS INT) >= 11 -- SQL 2012
BEGIN
  SET @sql = '
  SELECT type, mem_type, CAST(SUM(pages_mb)/1024. AS DECIMAL(20, 2)) as pages_mb
  FROM  
  (SELECT type, pages_kb as pages, virtual_memory_committed_kb as virtual, awe_allocated_kb as awe FROM sys.dm_os_memory_clerks WHERE type <> ''MEMORYCLERK_SQLQERESERVATIONS'') AS t  
  UNPIVOT (pages_mb FOR mem_type IN (pages, virtual, awe)  
  )AS unpvt
  WHERE unpvt.pages_mb > 0
  GROUP BY type, mem_type'
END
ELSE
BEGIN
  SET @sql = '
  SELECT type, mem_type, CAST(SUM(pages_mb)/1024. AS DECIMAL(20, 2)) as pages_mb
  FROM  
  (SELECT type, single_pages_kb as single_pages, multi_pages_kb as multi_pages, virtual_memory_committed_kb as virtual, awe_allocated_kb as awe FROM sys.dm_os_memory_clerks WHERE type <> ''MEMORYCLERK_SQLQERESERVATIS'') AS t  
  UNPIVOT (pages_mb FOR mem_type IN (single_pages, multi_pages, virtual, awe)
  )AS unpvt
  WHERE unpvt.pages_mb > 0
  GROUP BY type, mem_type'
END

print @sql 
INSERT #mem_clerks(type, mem_type, pages_mb)
EXEC(@SQL)

-- Build Memory Tree
DECLARE @MemTree TABLE (Id int, ParentId int, counter_name nvarchar(128), formatted_value NUMERIC(20, 2), shortname NVARCHAR(20))

--->>> EXTRA MEMORY (outside of the Buffer Pool) ---  ly for SQL 2008R2 and older
IF CAST(SUBSTRING(@Version, 1, CHARINDEX('.', @Version, 1)-1) AS INT) < 11
BEGIN
  -- Level 1: Total
  INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
  SELECT 
    Id = 2000,
    ParentId = NULL,
    counter_name = 'Extra Server Memory', 
    formatted_value = SUM(pages_mb),
    shortname = 'Extra'
  FROM #mem_clerks mc
  LEFT JOIN #mem_clerks_desc mcd  
  on mcd.type = mc.type
  WHERE mem_type = 'multi_pages'
  -- Level 2: Detailed
  INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
  SELECT TOP (@OtherClerksTop)
    Id = 2100,
    ParentId = 2000,
    counter_name = ISNULL(descripti, mc.type), 
    formatted_value = pages_mb,
    shortname = 'Extra'
  FROM #mem_clerks mc
  LEFT JOIN #mem_clerks_desc mcd  
  on mcd.type = mc.type
  WHERE mem_type = 'multi_pages'
    AND pages_mb > 1.
  ORDER BY pages_mb DESC

  -- Level 2: 'Other Server Memory Extra' = 'Server Memory (Extra)' - SUM(Children of 'Server Memory (Extra)')
  INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
  SELECT
    Id = 2110,
    ParentId = 2000,
    counter_name = '<Other Memory Clerks>', 
    formatted_value = (SELECT SSM.formatted_value FROM @MemTree SSM WHERE Id = 2000) - SUM(formatted_value),
    shortname = 'Other Extra'
  FROM @MemTree 
  WHERE ParentId = 2000
END
---<<< EXTRA MEMORY (outside of the Buffer Pool) ---  ly for SQL 2008R2 and older

------ MAIN
-- Level 1
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
SELECT 
  Id = 1000,
  ParentId = NULL,
  counter_name, 
  formatted_value,
  shortname
FROM @Perf
WHERE object_name = 'Memory Manager' AND 
  counter_name IN ('Target Server Memory')

-- Level 2
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
SELECT
  Id = CASE WHEN counter_name = 'Maximum Workspace Memory' THEN 1100 ELSE 1200 END,
  ParentId = 1000,
  counter_name, 
  formatted_value,
  shortname
FROM @Perf
WHERE object_name = 'Memory Manager' AND 
  counter_name IN ('Total Server Memory', 'Maximum Workspace Memory') 
UNION ALL
SELECT 
  Id = 1150,
  ParentId = 1000,
  counter_name = 'Foreign Node Memory', 
  formatted_value = SUM(formatted_value),
  shortname = 'Foreign'
FROM @Perf
WHERE object_name = 'Memory Node' AND 
  counter_name IN ('Foreign Node Memory')
HAVING SUM(formatted_value) > 0

-- Level 3
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
SELECT
  Id = CASE counter_name 
           WHEN 'Granted Workspace Memory' THEN 1110 
           WHEN 'Stolen Server Memory' THEN 1220 
           ELSE 1210
         END,
  ParentId = CASE counter_name 
               WHEN 'Granted Workspace Memory' THEN 1100 
               ELSE 1200 
             END,
  counter_name, 
  formatted_value,
  shortname
FROM @Perf
WHERE object_name = 'Memory Manager' 
  AND counter_name IN ('Stolen Server Memory', 'Database Cache Memory', 'Free Memory', 'Granted Workspace Memory')

-- Level 4
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
SELECT
  Id = 1225,
  ParentId = 1220,
  counter_name = p.object_name, 
  formatted_value = SUM(formatted_value) - SUM(ISNULL(mc.pages_mb, 0)), -- For SQL 2008 R2 and older subtract multi_pages
  p.shortname
FROM @Perf p
LEFT JOIN
(
  SELECT descripti as instance_name, pages_mb
  FROM #mem_clerks_desc mcd
      INNER JOIN #mem_clerks mc  
      on mc.type = mcd.type
  WHERE mc.mem_type = 'multi_pages' -- For SQL 2008 R2 and older
    AND ISNULL(mcd.is_perf_counter, 0) = 1
) mc  
on mc.instance_name = p.instance_name 
WHERE p.object_name = 'Plan Cache' 
  AND p.counter_name IN ('Cache')
  AND p.instance_name <> '_Total'
GROUP BY p.object_name, p.shortname

UNION ALL

SELECT
  Id = 1222,
  ParentId = 1220,
  p.counter_name, 
  formatted_value = p.formatted_value - ISNULL(mc.pages_mb, 0), -- For SQL 2008 R2 and older subtract multi_pages
  shortname
FROM @Perf p
LEFT JOIN
(
  SELECT descripti as counter_name, pages_mb
  FROM #mem_clerks_desc mcd
      INNER JOIN #mem_clerks mc  
      on mc.type = mcd.type
  WHERE mc.mem_type = 'multi_pages' -- For SQL 2008 R2 and older
    AND ISNULL(mcd.is_perf_counter, 0) = 1
) mc  
on mc.counter_name = p.counter_name 
WHERE ((object_name = 'Memory Manager' AND shortname IN ('Cnecti', 'Lock', 'Optimizer', 'Log'))
  )
  AND ISNULL(formatted_value, 0) > 0

UNION ALL

SELECT  -- Memory Clerks (SQL 2008)
    Id = 1222,
    ParentId = 1220,
    T.counter_name,
    T.formatted_value,
    shortname = 'memory clerks'
FROM
(
  SELECT TOP (@OtherClerksTop)
    counter_name = ISNULL(mcd.descripti, mc.type), --    ' '   mc.mem_type, 
    formatted_value = mc.pages_mb
  FROM #mem_clerks mc
  LEFT JOIN #mem_clerks_desc mcd  
  on mcd.type = mc.type
  WHERE CAST(SUBSTRING(@Version, 1, CHARINDEX('.', @Version, 1)-1) AS INT) < 11 -- SQL 2008 and older
    AND mc.mem_type = 'single_pages'  -- SQL 2008 and older
    AND ISNULL(mcd.is_perf_counter, 0) = 0
  ORDER BY pages_mb DESC
) T

UNION ALL

SELECT   -- Memory Clerks (SQL 2012 )
    Id = 1222,
    ParentId = 1220,
    T.counter_name,
    T.formatted_value,
    shortname = 'memory clerks'
FROM
(
  SELECT TOP (@OtherClerksTop)
    counter_name = ISNULL(mcd.descripti, mc.type)   CASE WHEN mc.mem_type <> 'pages' THEN ' ('   mc.mem_type   ')' ELSE '' END, 
    formatted_value = mc.pages_mb
  FROM #mem_clerks mc
  LEFT JOIN #mem_clerks_desc mcd  
  on mcd.type = mc.type
  WHERE CAST(SUBSTRING(@Version, 1, CHARINDEX('.', @Version, 1)-1) AS INT) >= 11 -- SQL 2012 and newer
    AND ((mc.mem_type = 'pages' AND ISNULL(mcd.is_perf_counter, 0) = 0)
      OR mc.mem_type IN ('virtual', 'awe')
    )
  ORDER BY pages_mb DESC
) T

UNION ALL

SELECT
  Id = 1112,
  ParentId = 1110,
  counter_name, 
  formatted_value,
  shortname
FROM @Perf
WHERE object_name = 'Memory Manager' 
  AND shortname IN ('Reserved')
UNION ALL
SELECT
  Id = P.ParentID   1,
  ParentID = P.ParentID,
  'Used Workspace Memory' AS counter_name,
  SUM(used_memory_kb)/1024. as formatted_value,
  NULL AS shortname
FROM sys.dm_exec_query_resource_semaphores 
  CROSS JOIN (SELECT 1220 AS ParentID UNION ALL SELECT 1110) P
GROUP BY P.ParentID

-- Level 4 -- 'Other Stolen Server Memory' = 'Stolen Server Memory' - SUM(Children of 'Stolen Server Memory')
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
SELECT
  Id = 1222,
  ParentId = 1220,
  counter_name = '<Other Memory Clerks>', 
  formatted_value = (SELECT SSM.formatted_value FROM @MemTree SSM WHERE Id = 1220) - SUM(formatted_value),
  shortname = 'Other Stolen'
FROM @MemTree 
WHERE ParentId = 1220

-- Level 5
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
SELECT
  Id = CASE WHEN p.instance_name = 'SQL Plans' THEN 1226 ELSE 1230 END,
  ParentId = 1225,
  counter_name = p.instance_name, 
  formatted_value = formatted_value - ISNULL(mc.pages_mb, 0), -- For SQL 2008 R2 and older subtract multi_pages
  p.shortname
FROM @Perf p
LEFT JOIN
(
  SELECT descripti as instance_name, pages_mb
  FROM #mem_clerks_desc mcd
      INNER JOIN #mem_clerks mc  
      on mc.type = mcd.type 
  WHERE mc.mem_type = 'multi_pages' -- For SQL 2008 R2 and older
    AND ISNULL(mcd.is_perf_counter, 0) = 1
) mc  
on mc.instance_name = p.instance_name 
WHERE p.object_name = 'Plan Cache' 
  AND p.counter_name IN ('Cache')
  AND p.instance_name <> '_Total'

-- Level 6
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, shortname)
SELECT
  Id = 1227,
  ParentId = 1226,
  counter_name,
  formatted_value,
  shortname
FROM @Perf
WHERE (object_name = 'Memory Manager' AND shortname = 'SQL')  -- SQL Cache Memory
    OR object_name = 'Cursor'

-- Results:

-- PLE and Memory Grants
SELECT
    [Counter Name] = P.counter_name   ISNULL(' (Node: '   NULLIF(P.instance_name, '')   ')', ''), 
    cntr_value as Value,
    RecommendedMinimum = 
        CASE 
            WHEN P.counter_name = 'Page life expectancy' AND R.Value <= 300 -- no less than 300
                THEN 300
            WHEN P.counter_name = 'Page life expectancy' AND R.Value > 300 
                THEN R.Value
            ELSE NULL 
        END
FROM @Perf P
LEFT JOIN -- Recommended PLE calculatis
    (
        SELECT 
            object_name, 
            counter_name, 
            instance_name, 
            CEILING(formatted_value/4096.*5) * 60 AS Value -- 300 per every 4GB of Buffer Pool memory or around 60 secds (1 minute) per every 819MB
        FROM @Perf PD
        WHERE counter_name = 'Database Cache Memory'
    ) R  
    on R.object_name = P.object_name 
       AND R.instance_name = P.instance_name
WHERE 
  (P.object_name = 'Memory Manager' 
  AND P.counter_name IN ('Memory Grants Outstanding', 'Memory Grants Pending', 'Page life expectancy')
  )
    OR -- For NUMA
  (
    P.object_name = 'Memory Node' AND P.counter_name = 'Page life expectancy'
    AND (
        SELECT COUNT(DISTINCT instance_name)
        FROM @Perf 
        WHERE object_name = 'Memory Node'
    ) > 1
  )
ORDER BY P.counter_name DESC, P.instance_name

-- Get physical memory
-- You can also extract this informati from sys.dm_os_sys_info but the column names have changed starting from 2012
IF OBJECT_ID('tempdb..#msver') IS NOT NULL DROP TABLE #msver
CREATE TABLE #msver(ID int, Name  sysname, Internal_Value int, Value nvarchar(512))
INSERT #msver EXEC master.dbo.xp_msver 'PhysicalMemory'

DECLARE @locked_page_allocations_mb DECIMAL(20, 2)
IF OBJECT_ID('sys.dm_os_process_memory') IS NOT NULL
  SELECT @locked_page_allocations_mb = locked_page_allocations_kb / 1024.FROM sys.dm_os_process_memory

-- Physical memory, cfig parameters and Target memory
SELECT 
  min_server_mb = (SELECT CAST(value_in_use AS DECIMAL(20, 2)) FROM sys.configurations WHERE name = 'min server memory (MB)'),
  max_server_mb = (SELECT CAST(value_in_use AS DECIMAL(20, 2)) FROM sys.configurations WHERE name = 'max server memory (MB)'),
  target_mb = (SELECT formatted_value FROM @Perf WHERE object_name = 'Memory Manager' AND counter_name IN ('Target Server Memory')),
  physical_mb = CAST(Internal_Value AS DECIMAL(20, 2)),
  locked_pages_mb = @locked_page_allocations_mb
FROM #msver

-- Memory tree
;WITH CTE
AS
(
  SELECT 0 as lvl, counter_name, formatted_value, Id, NULL AS ParentId, shortname, formatted_value as TargetServerMemory, CAST(NULL AS DECIMAL(20,2)) As Perc, CAST(NULL AS DECIMAL(20,2)) As PercOfTarget
  FROM @MemTree
  WHERE ParentId IS NULL
  UNION ALL
  SELECT CTE.lvl 1,
    CAST(REPLICATE(' ', 6*(CTE.lvl))   NCHAR(124)   REPLICATE(NCHAR(183), 3)   MT.counter_name AS NVARCHAR(128)), 
    MT.formatted_value, MT.Id, MT.ParentId, MT.shortname, CTE.TargetServerMemory,
    CAST(ISNULL(100.0*MT.formatted_value/NULLIF(CTE.formatted_value, 0),0) AS DECIMAL(20,2)) AS Perc,
    CAST(ISNULL(100.0*MT.formatted_value/NULLIF(CTE.TargetServerMemory, 0),0) AS DECIMAL(20,2)) AS PercOfTarget
  FROM @MemTree MT
  INNER JOIN CTE  
  on MT.ParentId = CTE.Id
)
SELECT 
  counter_name AS [Counter Name], CASE WHEN formatted_value > 0 THEN formatted_value ELSE NULL END AS [Memory MB], Perc AS [% of Parent], CASE WHEN lvl >= 2 THEN PercOfTarget ELSE NULL END AS [% of Target]
FROM CTE
ORDER BY ISNULL(Id, 10000), formatted_value DESC

1. Спасибо @sepupic. Я обновил версии SQL Server в описании проблемы. Я запускаю команду «ВЫБРАТЬ ВО временную таблицу» и она работает быстрее, чем «ВЫБРАТЬ *» на обоих серверах, но консоль управления установлена на том же сервере, что и SQL Server: имеет смысл для вас?. С другой стороны, SQL 2014 работает быстрее (в два раза), чем SQL 2008 в предложении «SELECT INTO temporary», но самый медленный в предложении «SELECT *». Я прилагаю файл сравнения Excel.

2. Это означает, что ваш новый сервер работает быстрее. У вас проблемы только с вашим клиентом. Вы не сравнивали статистику ожидания (я дал вам ссылку на запрос Рэндала), но я уверен, что вы увидите ожидание async_network на новом сервере

3. Я просто запускаю статистику ожидания. ТОП-3 статистики на новом сервере (2014): PAGEIOLATCH_SH, CXPACKET и ASYNC_NETWORK_IO ТОП-3 на более старом сервере (2008, который быстрее): CXPACKET, ASYNC_NETWORK_IO и PREEMPTIVE_OS_WAITFORSINGLEOBJECT

4. Было бы лучше, если бы вы написали% от каждого типа ожидания. В любом случае, если PAGEIOLATCH_SH является основным типом ожидания на новом сервере, это может означать, что у него меньше памяти, чем у вашего старого сервера. Поскольку он находится в виртуальной среде, я сомневаюсь, что у вас одинаковая память на обоих серверах, даже если вы видите одно и то же настроенное значение

5. Вы можете проверить, используется ли ваша реальная память, выполнив этот выбор: выберите приведение(физическая память_in_use_kb / 1024. / 1024 как десятичное число (10,2)) Физическая память_in_use_gb, приведение(locked_page_allocations_kb / 1024. / 1024 как десятичное число(10,2)) locked_page_allocations_Gb, приведение(virtual_address_space_committed_kb / 1024. / 1024 как десятичное число (10,2))virtual_address_space_committed_Gb из sys.dm_os_process_memory