Usuario
Lentitud en apliacion que usa SQL 2014

Pregunta
-
saludos:
Tengo un servidor Windows Server 2014 con 2 vcpu y 6 GB de ram en el cual e instalado SQL server standard (Este servidor esta en un proveedor Cloud)
Los usuarios tienen la aplicacion instalada en cada pc y la aplicacion tiene un modulo de configurador en el cual conectan a la base de datos de SQL con la ip publica, el nombre de la BD.
Los usuarios reportan lentitud en la aplicacion sin embargo el internet me da latencia de 8 ml a 10 max.
Puedo hacer algo en el servidor o el cliente para que se ejecute mas rapido?
Si no se hace por Terminal server no hay forma de volverlo mas rapido?
Agradezco sus opiniones
Juan Pablo Vidal http://juanvidal.wordpress.com
Todas las respuestas
-
-
si estas usando un servidor web, seria entre comillas mejor usar una conexion directa no desde ip.
ya que lo que esta pasando supongo, ya que no se la arquitectura de tu software es que haces dos llamados.
uno al server principal (La pc que se conecta a la web)
dos el cliente en red local
por lo tanto el cliente el pide la info al server que a su vez pide la info al host. entonces ay esta la lentitud. y mientras mas datos pidas mas lento será, ya que no es lo mismo pedir a lo mejor un texto a pedir una imagen.
como recomendación as el llamado directamente al host web, además de ver los protocolos y velocidad asignada al server.
Manuel Zaragoza
-
-
Hola Juan Vidal IT:
Lo primero, es que tener mucha memoria, evidentemente, irá en benefició del la velocidad de la aplicación en tanto en cuanto se consuman datos. Pero si presenta problemas de lentitud, hay que diferenciar un poco si la lentitud es por cantidad de memoria, por procesamiento, o por problemas derivados de la red, o lecturas en disco. Todo esto atribuyendo el empeoramiento al sql, que quizá no sea el culpable.
Si puedes ejecutar consultas:
select (physical_memory_in_use_kb/1024)Phy_Memory_usedby_Sqlserver_MB, (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB, (virtual_address_space_committed_kb/1024 )Total_Memory_UsedBySQLServer_MB, process_physical_memory_low, process_virtual_memory_low from sys. dm_os_process_memory
Mucho más detallado y elaborado:
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQLServer_Memory_Information.sql
SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET LOCK_TIMEOUT 10000; DECLARE @ServiceName nvarchar(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' 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)', 'Connection 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)') ) OR (object_name LIKE @ServiceName + 'Cursor Manager by Type%' AND counter_name = 'Cursor memory usage' AND instance_name = '_Total' ); -- Add unit to 'Cursor memory usage' UPDATE @Perf SET counter_name = counter_name + ' (KB)' WHERE counter_name = 'Cursor memory usage'; -- Convert 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'); -- Build Memory Tree DECLARE @MemTree TABLE ( Id int, ParentId int, counter_name nvarchar(128), formatted_value numeric(20, 2), shortname nvarchar(20) ); -- Level 5 INSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname) SELECT Id = 1226, ParentId = 1225, instance_name AS counter_name, formatted_value, shortname FROM @Perf WHERE object_name = 'Plan Cache' AND counter_name IN ('Cache') AND instance_name <> '_Total'; -- Level 4 INSERT @MemTree (Id, ParentId, counter_name, formatted_value, shortname) SELECT Id = 1225, ParentId = 1220, 'Plan ' + counter_name AS counter_name, formatted_value, shortname FROM @Perf WHERE object_name = 'Plan Cache' AND counter_name IN ('Cache') AND instance_name = '_Total' UNION ALL SELECT Id = 1222, ParentId = 1220, counter_name, formatted_value, shortname FROM @Perf WHERE object_name = 'Cursor' OR (object_name = 'Memory Manager' AND shortname IN ('Connection', 'Lock', 'Optimizer', 'SQL')) 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 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 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'); -- 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 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; -- 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 calculations (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 seconds (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 information 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'; -- Physical memory, config 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)) 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, 4)) AS Perc, CAST(NULL AS decimal(20, 4)) 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(1.0 * MT.formatted_value / NULLIF(CTE.formatted_value, 0), 0) AS decimal(20, 4)) AS Perc, CAST(ISNULL(1.0 * MT.formatted_value / NULLIF(CTE.TargetServerMemory, 0), 0) AS decimal(20, 4)) 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;
Pero muchas bases de datos, de Sql Server, (ediciones express), sirven datos, a una velocidad muy buena con un giga de ram, para aplicaciones de empresas no muy grandes, que no necesiten un volumen de datos grande.
Por tanto si la demora es al realizar cosas sencillas (volumén de datos pequeño), no tiene que ver el uso de la memoria.
-
-
Sin entrar a buscar nada elaborado, (existen cosas muy buenas pero requieren de bastante trabajo y/o herramientas de terceros que pueden ayudar también), si tienes instalado SQL Server Management Studio, puedes buscar los informes de rendimiento, que pueden darte "información", muy interesante, para ir buscando los puntos de posible fallo.
Sobre el explorador de objetos, botón derecho->Monitor de actividad
PROCESOS, ESPERAS DE RECURSOS, E/S DE ARCHIVOS DE DATOS, CONSULTAS COSTOSAS RECIENTES, CONSULTAS COSTOSAS ACTIVAS
También puedes tener informes: