Lansweeper SQL Database Query

[sukaitsu]

Our SQLDBA detected this query as one taking 12,000+ seconds to run with one of their SQL Performance tools. Please look into the structure of this query as they have determined it to be the cause of our SQL Database slow downs. I will include our hardware setup, so you know its not due to a lack of resources.

SQL Database Server:

2 Virtual Servers with an identical setup clustered together for fail over protection.

CPU: 8 Cores Intel Xeon CPU E5-2670 @2.60GHz
RAM: 30.0GB
OS: Windows Server 2008 R2 Entperise
NET: 1GB Virtual NIC assigned to the SQL Instance
SQL Ver.: SQL Server 2012 Enterprise

[size=6]Web Server: [/size]

3 Dedicated Virtual Machines sharing the load in an Application Request Routing. 2 host the website and one for routing the traffic.

CPU: 4 Cores Intel Xeon CPU E5-2670 @2.60GHz
RAM: 6.0GB
OS: Windows Server 2012 R2
NET: 1GB NIC routed to the SQL DB, 1GB NIC for Site Access
SLB: Lease Request Response (12 second poll)

Web Host Config:
Web IIS 8.5
Application Request Routing 3.0
Web Farm Framework 2.2
Web Deploy 3.5

20 Scan Servers with a minimum of 16GB of RAM each, and 2 1GB Ethernet NICs Teamed (Most with 2 10GB Fiber NICs teamed).

Even when the Lansweeper SQL instance is the only one active on a node in the cluster, the CPU usage averages 85% or higher. The database has been pinpointed to the root cause of the slow downs.

We are sitting at 88,427 licensed assets.

Corrupted Query below:

SELECT DISTINCT
a.AssetID,
a.AssetUnique,
CASE WHEN b.macCount IS NULL THEN 0 ELSE b.macCount END AS macCount,
CASE WHEN c.SnmpOID IS NULL OR c.SnmpOid = '' THEN 0 ELSE 1 END AS OidFound
FROM (SELECT AssetID, COUNT(*) macCount FROM tblAssetMacAddress m GROUP BY AssetID) b
RIGHT JOIN tblAssets a on a.AssetID = b.AssetID
JOIN tblAssetCustom c on a.AssetID = c.AssetID
LEFT JOIN tblAssetMacAddress m ON a.AssetID = m.AssetID
WHERE
a.Assettype <> @windows
AND
a.IPAddress <> '' AND a.IPAddress IS NOT NULL
AND
(
(
a.IPAddress=@ip
AND
(c.HttpServer IS NULL OR c.HttpServer = '' OR @httpServer = CAST('' AS nvarchar(500 )) OR c.HttpServer = @httpServer ) AND
(c.HttpsServer IS NULL OR c.HttpsServer = '' OR @httpsServer = CAST('' AS nvarchar(500 )) OR c.HttpsServer = @httpsServer ) AND
(c.HTTPTitle IS NULL OR c.HTTPTitle = '' OR @httpTitle = CAST('' AS nvarchar(1000)) OR c.HTTPTitle = @httpTitle ) AND
(c.FTPheader IS NULL OR c.FTPheader = '' OR @ftpHeader = CAST('' AS nvarchar(500 )) OR c.FTPheader = @ftpHeader ) AND
(c.SMTPheader IS NULL OR c.SMTPheader = '' OR @smtpHeader = CAST('' AS nvarchar(500 )) OR c.SMTPheader = @smtpHeader ) AND
(c.SSHServer IS NULL OR c.SSHServer = '' OR @sshServer = CAST('' AS nvarchar(255 )) OR c.SSHServer = @sshServer ) AND
(c.Serialnumber IS NULL OR c.Serialnumber = '' OR @serialnumber = CAST('' AS nvarchar(100 )) OR c.Serialnumber = @serialnumber)
AND (CASE WHEN b.macCount IS NULL THEN 0 ELSE b.macCount END <= 0 AND (a.Mac = '' OR a.Mac IS NULL))
)
OR
m.Mac IN (cast(@mac_0 AS nvarchar(300)),cast(@mac_1 AS nvarchar(300)),cast(@mac_2 AS nvarchar(300)),cast(@mac_3 AS nvarchar(300)),cast(@mac_4 AS nvarchar(300)),cast(@mac_5 AS nvarchar(300)),cast(@mac_6 AS nvarchar(300)),cast(@mac_7 AS nvarchar(300)),cast(@mac_8 AS nvarchar(300)),cast(@mac_9 AS nvarchar(300)),cast(@mac_10 AS nvarchar(300)),cast(@mac_11 AS nvarchar(300)),cast(@mac_12 AS nvarchar(300)),cast(@mac_13 AS nvarchar(300)),cast(@mac_14 AS nvarchar(300)),cast(@mac_15 AS nvarchar(300)),cast(@mac_16 AS nvarchar(300)),cast(@mac_17 AS nvarchar(300)),cast(@mac_18 AS nvarchar(300)),cast(@mac_19 AS nvarchar(300)),cast(@mac_20 AS nvarchar(300)),cast(@mac_21 AS nvarchar(300)),cast(@mac_22 AS nvarchar(300)),cast(@mac_23 AS nvarchar(300)),cast(@mac_24 AS nvarchar(300)),cast(@mac_25 AS nvarchar(300)),cast(@mac_26 AS nvarchar(300)),cast(@mac_27 AS nvarchar(300)),cast(@mac_28 AS nvarchar(300)),cast(@mac_29 AS nvarchar(300)),cast(@mac_30 AS nvarchar(300)),cast(@mac_31 AS nvarchar(300)),cast(@mac_32 AS nvarchar(300)),cast(@mac_33 AS nvarchar(300)),cast(@mac_34 AS nvarchar(300)),cast(@mac_35 AS nvarchar(300)),cast(@mac_36 AS nvarchar(300)),cast(@mac_37 AS nvarchar(300)),cast(@mac_38 AS nvarchar(300)),cast(@mac_39 AS nvarchar(300)),cast(@mac_40 AS nvarchar(300)),cast(@mac_41 AS nvarchar(300)),cast(@mac_42 AS nvarchar(300)),cast(@mac_43 AS nvarchar(300)),cast(@mac_44 AS nvarchar(300)),cast(@mac_45 AS nvarchar(300)),cast(@mac_46 AS nvarchar(300)),cast(@mac_47 AS nvarchar(300)),cast(@mac_48 AS nvarchar(300)),cast(@mac_49 AS nvarchar(300)),cast(@mac_50 AS nvarchar(300)),cast(@mac_51 AS nvarchar(300)),cast(@mac_52 AS nvarchar(300)),cast(@mac_53 AS nvarchar(300)),cast(@mac_54 AS nvarchar(300)),cast(@mac_55 AS nvarchar(300)),cast(@mac_56 AS nvarchar(300)),cast(@mac_57 AS nvarchar(300)),cast(@mac_58 AS nvarchar(300)),cast(@mac_59 AS nvarchar(300)),cast(@mac_60 AS nvarchar(300)),cast(@mac_61 AS nvarchar(300)),cast(@mac_62 AS nvarchar(300)),cast(@mac_63 AS nvarchar(300)),cast(@mac_64 AS nvarchar(300)),cast(@mac_65 AS nvarchar(300)),cast(@mac_66 AS nvarchar(300)),cast(@mac_67 AS nvarchar(300)),cast(@mac_68 AS nvarchar(300)),cast(@mac_69 AS nvarchar(300)),cast(@mac_70 AS nvarchar(300)),cast(@mac_71 AS nvarchar(300)),cast(@mac_72 AS nvarchar(300)),cast(@mac_73 AS nvarchar(300)),cast(@mac_74 AS nvarchar(300)),cast(@mac_75 AS nvarchar(300)),cast(@mac_76 AS nvarchar(300)),cast(@mac_77 AS nvarchar(300)),cast(@mac_78 AS nvarchar(300)),cast(@mac_79 AS nvarchar(300)),cast(@mac_80 AS nvarchar(300)),cast(@mac_81 AS nvarchar(300)),cast(@mac_82 AS nvarchar(300)),cast(@mac_83 AS nvarchar(300)),cast(@mac_84 AS nvarchar(300)),cast(@mac_85 AS nvarchar(300)),cast(@mac_86 AS nvarchar(300)),cast(@mac_87 AS nvarchar(300)),cast(@mac_88 AS nvarchar(300)),cast(@mac_89 AS nvarchar(300)),cast(@mac_90 AS nvarchar(300)),cast(@mac_91 AS nvarchar(300)),cast(@mac_92 AS nvarchar(300)),cast(@mac_93 AS nvarchar(300)),cast(@mac_94 AS nvarchar(300)),cast(@mac_95 AS nvarchar(300)),cast(@mac_96 AS nvarchar(300)),cast(@mac_97 AS nvarchar(300)),cast(@mac_98 AS nvarchar(300)),cast(@mac_99 AS nvarchar(300)),cast(@mac_100 AS nvarchar(300)),cast(@mac_101 AS nvarchar(300)),cast(@mac_102 AS nvarchar(300)),cast(@mac_103 AS nvarchar(300)),cast(@mac_104 AS nvarchar(300)),cast(@mac_105 AS nvarchar(300)),cast(@mac_106 AS nvarchar(300)),cast(@mac_107 AS nvarchar(300)),cast(@mac_108 AS nvarchar(300)),cast(@mac_109 AS nvarchar(300)),cast(@mac_110 AS nvarchar(300)),cast(@mac_111 AS nvarchar(300)),cast(@mac_112 AS nvarchar(300)),cast(@mac_113 AS nvarchar(300)),cast(@mac_114 AS nvarchar(300)),cast(@mac_115 AS nvarchar(300)),cast(@mac_116 AS nvarchar(300)),cast(@mac_117 AS nvarchar(300)),cast(@mac_118 AS nvarchar(300)),cast(@mac_119 AS nvarchar(300)),cast(@mac_120 AS nvarchar(300)),cast(@mac_121 AS nvarchar(300)),cast(@mac_122 AS nvarchar(300)),cast(@mac_123 AS nvarchar(300)),cast(@mac_124 AS nvarchar(300)),cast(@mac_125 AS nvarchar(300)),cast(@mac_126 AS nvarchar(300)),cast(@mac_127 AS nvarchar(300)),cast(@mac_128 AS nvarchar(300)),cast(@mac_129 AS nvarchar(300)),cast(@mac_130 AS nvarchar(300)),cast(@mac_131 AS nvarchar(300)),cast(@mac_132 AS nvarchar(300)),cast(@mac_133 AS nvarchar(300)),cast(@mac_134 AS nvarchar(300)),cast(@mac_135 AS nvarchar(300)),cast(@mac_136 AS nvarchar(300)),cast(@mac_137 AS nvarchar(300)),cast(@mac_138 AS nvarchar(300)),cast(@mac_139 AS nvarchar(300)),cast(@mac_140 AS nvarchar(300)),cast(@mac_141 AS nvarchar(300)),cast(@mac_142 AS nvarchar(300)),cast(@mac_143 AS nvarchar(300)),cast(@mac_144 AS nvarchar(300)),cast(@mac_145 AS nvarchar(300)),cast(@mac_146 AS nvarchar(300)),cast(@mac_147 AS nvarchar(300)),cast(@mac_148 AS nvarchar(300)),cast(@mac_149 AS nvarchar(300)),cast(@mac_150 AS nvarchar(300)),cast(@mac_151 AS nvarchar(300)),cast(@mac_152 AS nvarchar(300)),cast(@mac_153 AS nvarchar(300)),cast(@mac_154 AS nvarchar(300)),cast(@mac_155 AS nvarchar(300)),cast(@mac_156 AS nvarchar(300)),cast(@mac_157 AS nvarchar(300)),cast(@mac_158 AS nvarchar(300)),cast(@mac_159 AS nvarchar(300)),cast(@mac_160 AS nvarchar(300)),cast(@mac_161 AS nvarchar(300)),cast(@mac_162 AS nvarchar(300)),cast(@mac_163 AS nvarchar(300)),cast(@mac_164 AS nvarchar(300)),cast(@mac_165 AS nvarchar(300)),cast(@mac_166 AS nvarchar(300)),cast(@mac_167 AS nvarchar(300)),cast(@mac_168 AS nvarchar(300)),cast(@mac_169 AS nvarchar(300)),cast(@mac_170 AS nvarchar(300)),cast(@mac_171 AS nvarchar(300)),cast(@mac_172 AS nvarchar(300)),cast(@mac_173 AS nvarchar(300)),cast(@mac_174 AS nvarchar(300)),cast(@mac_175 AS nvarchar(300)),cast(@mac_176 AS nvarchar(300)),cast(@mac_177 AS nvarchar(300)),cast(@mac_178 AS nvarchar(300)),cast(@mac_179 AS nvarchar(300)),cast(@mac_180 AS nvarchar(300)),cast(@mac_181 AS nvarchar(300)),cast(@mac_182 AS nvarchar(300)),cast(@mac_183 AS nvarchar(300)),cast(@mac_184 AS nvarchar(300)),cast(@mac_185 AS nvarchar(300)),cast(@mac_186 AS nvarchar(300)),cast(@mac_187 AS nvarchar(300)),cast(@mac_188 AS nvarchar(300)),cast(@mac_189 AS nvarchar(300)),cast(@mac_190 AS nvarchar(300)),cast(@mac_191 AS nvarchar(300)),cast(@mac_192 AS nvarchar(300)),cast(@mac_193 AS nvarchar(300)),cast(@mac_194 AS nvarchar(300)),cast(@mac_195 AS nvarchar(300)),cast(@mac_196 AS nvarchar(300)),cast(@mac_197 AS nvarchar(300)),cast(@mac_198 AS nvarchar(300)),cast(@mac_199 AS nvarchar(300)),cast(@mac_200 AS nvarchar(300)),cast(@mac_201 AS nvarchar(300)),cast(@mac_202 AS nvarchar(300)),cast(@mac_203 AS nvarchar(300)),cast(@mac_204 AS nvarchar(300)),cast(@mac_205 AS nvarchar(300)),cast(@mac_206 AS nvarchar(300)),cast(@mac_207 AS nvarchar(300)),cast(@mac_208 AS nvarchar(300)),cast(@mac_209 AS nvarchar(300)),cast(@mac_210 AS nvarchar(300)),cast(@mac_211 AS nvarchar(300)),cast(@mac_212 AS nvarchar(300)),cast(@mac_213 AS nvarchar(300)),cast(@mac_214 AS nvarchar(300)),cast(@mac_215 AS nvarchar(300)),cast(@mac_216 AS nvarchar(300)),cast(@mac_217 AS nvarchar(300)),cast(@mac_218 AS nvarchar(300)),cast(@mac_219 AS nvarchar(300)),cast(@mac_220 AS nvarchar(300)),cast(@mac_221 AS nvarchar(300)),cast(@mac_222 AS nvarchar(300)),cast(@mac_223 AS nvarchar(300)),cast(@mac_224 AS nvarchar(300)),cast(@mac_225 AS nvarchar(300)),cast(@mac_226 AS nvarchar(300)),cast(@mac_227 AS nvarchar(300)),cast(@mac_228 AS nvarchar(300)),cast(@mac_229 AS nvarchar(300)),cast(@mac_230 AS nvarchar(300)),cast(@mac_231 AS nvarchar(300)),cast(@mac_232 AS nvarchar(300)),cast(@mac_233 AS nvarchar(300)),cast(@mac_234 AS nvarchar(300)),cast(@mac_235 AS nvarchar(300)),cast(@mac_236 AS nvarchar(300)),cast(@mac_237 AS nvarchar(300)),cast(@mac_238 AS nvarchar(300)),cast(@mac_239 AS nvarchar(300)),cast(@mac_240 AS nvarchar(300)),cast(@mac_241 AS nvarchar(300)),cast(@mac_242 AS nvarchar(300)),cast(@mac_243 AS nvarchar(300)),cast(@mac_244 AS nvarchar(300)),cast(@mac_245 AS nvarchar(300)),cast(@mac_246 AS nvarchar(300)),cast(@mac_247 AS nvarchar(300)),cast(@mac_248 AS nvarchar(300)),cast(@mac_249 AS nvarchar(300)),cast(@mac_250 AS nvarchar(300)),cast(@mac_251 AS nvarchar(300)),cast(@mac_252 AS nvarchar(300)),cast(@mac_253 AS nvarchar(300)),cast(@mac_254 AS nvarchar(300)),cast(@mac_255 AS nvarchar(300)),cast(@mac_256 AS nvarchar(300)),cast(@mac_257 AS nvarchar(300)),cast(@mac_258 AS nvarchar(300)),cast(@mac_259 AS nvarchar(300)),cast(@mac_260 AS nvarchar(300)),cast(@mac_261 AS nvarchar(300)),cast(@mac_262 AS nvarchar(300)),cast(@mac_263 AS nvarchar(300)),cast(@mac_264 AS nvarchar(300)),cast(@mac_265 AS nvarchar(300)),cast(@mac_266 AS nvarchar(300)),cast(@mac_267 AS nvarchar(300)),cast(@mac_268 AS nvarchar(300)),cast(@mac_269 AS nvarchar(300)),cast(@mac_270 AS nvarchar(300)),cast(@mac_271 AS nvarchar(300)),cast(@mac_272 AS nvarchar(300)),cast(@mac_273 AS nvarchar(300)),cast(@mac_274 AS nvarchar(300)),cast(@mac_275 AS nvarchar(300)),cast(@mac_276 AS nvarchar(300)),cast(@mac_277 AS nvarchar(300)),cast(@mac_278 AS nvarchar(300)),cast(@mac_279 AS nvarchar(300)),cast(@mac_280 AS nvarchar(300)),cast(@mac_281 AS nvarchar(300)),cast(@mac_282 AS nvarchar(300)),cast(@mac_283 AS nvarchar(300)),cast(@mac_284 AS nvarchar(300)),cast(@mac_285 AS nvarchar(300)),cast(@mac_286 AS nvarchar(300)),cast(@mac_287 AS nvarchar(300)),cast(@mac_288 AS nvarchar(300)),cast(@mac_289 AS nvarchar(300)),cast(@mac_290 AS nvarchar(300)),cast(@mac_291 AS nvarchar(300)),cast(@mac_292 AS nvarchar(300)),cast(@mac_293 AS nvarchar(300)),cast(@mac_294 AS nvarchar(300)),cast(@mac_295 AS nvarchar(300)),cast(@mac_296 AS nvarchar(300)),cast(@mac_297 AS nvarchar(300)),cast(@mac_298 AS nvarchar(300)),cast(@mac_299 AS nvarchar(300)),cast(@mac_300 AS nvarchar(300)),cast(@mac_301 AS nvarchar(300)),cast(@mac_302 AS nvarchar(300)),cast(@mac_303 AS nvarchar(300)),cast(@mac_304 AS nvarchar(300)),cast(@mac_305 AS nvarchar(300)),cast(@mac_306 AS nvarchar(300)),cast(@mac_307 AS nvarchar(300)),cast(@mac_308 AS nvarchar(300)),cast(@mac_309 AS nvarchar(300)),cast(@mac_310 AS nvarchar(300)),cast(@mac_311 AS nvarchar(300)),cast(@mac_312 AS nvarchar(300)),cast(@mac_313 AS nvarchar(300)),cast(@mac_314 AS nvarchar(300)),cast(@mac_315 AS nvarchar(300)),cast(@mac_316 AS nvarchar(300)),cast(@mac_317 AS nvarchar(300)),cast(@mac_318 AS nvarchar(300)),cast(@mac_319 AS nvarchar(300)),cast(@mac_320 AS nvarchar(300)),cast(@mac_321 AS nvarchar(300)),cast(@mac_322 AS nvarchar(300)),cast(@mac_323 AS nvarchar(300)),cast(@mac_324 AS nvarchar(300)),cast(@mac_325 AS nvarchar(300)),cast(@mac_326 AS nvarchar(300)),cast(@mac_327 AS nvarchar(300)),cast(@mac_328 AS nvarchar(300)),cast(@mac_329 AS nvarchar(300)),cast(@mac_330 AS nvarchar(300)),cast(@mac_331 AS nvarchar(300)),cast(@mac_332 AS nvarchar(300)),cast(@mac_333 AS nvarchar(300)),cast(@mac_334 AS nvarchar(300)),cast(@mac_335 AS nvarchar(300)),cast(@mac_336 AS nvarchar(300)),cast(@mac_337 AS nvarchar(300)),cast(@mac_338 AS nvarchar(300)),cast(@mac_339 AS nvarchar(300)),cast(@mac_340 AS nvarchar(300)),cast(@mac_341 AS nvarchar(300)),cast(@mac_342 AS nvarchar(300)),cast(@mac_343 AS nvarchar(300)),cast(@mac_344 AS nvarchar(300)),cast(@mac_345 AS nvarchar(300)),cast(@mac_346 AS nvarchar(300)),cast(@mac_347 AS nvarchar(300)),cast(@mac_348 AS nvarchar(300)),cast(@mac_349 AS nvarchar(300)),cast(@mac_350 AS nvarchar(300)),cast(@mac_351 AS nvarchar(300)),cast(@mac_352 AS nvarchar(300)),cast(@mac_353 AS nvarchar(300)),cast(@mac_354 AS nvarchar(300)),cast(@mac_355 AS nvarchar(300)),cast(@mac_356 AS nvarchar(300)),cast(@mac_357 AS nvarchar(300)),cast(@mac_358 AS nvarchar(300)),cast(@mac_359 AS nvarchar(300)),cast(@mac_360 AS nvarchar(300)),cast(@mac_361 AS nvarchar(300)),cast(@mac_362 AS nvarchar(300)),cast(@mac_363 AS nvarchar(300)),cast(@mac_364 AS nvarchar(300)),cast(@mac_365 AS nvarchar(300)),cast(@mac_366 AS nvarchar(300)),cast(@mac_367 AS nvarchar(300)),cast(@mac_368 AS nvarchar(300)),cast(@mac_369 AS nvarchar(300)),cast(@mac_370 AS nvarchar(300)),cast(@mac_371 AS nvarchar(300)),cast(@mac_372 AS nvarchar(300)),cast(@mac_373 AS nvarchar(300)),cast(@mac_374 AS nvarchar(300)),cast(@mac_375 AS nvarchar(300)),cast(@mac_376 AS nvarchar(300)),cast(@mac_377 AS nvarchar(300)),cast(@mac_378 AS nvarchar(300)),cast(@mac_379 AS nvarchar(300)),cast(@mac_380 AS nvarchar(300)),cast(@mac_381 AS nvarchar(300)),cast(@mac_382 AS nvarchar(300)),cast(@mac_383 AS nvarchar(300)),cast(@mac_384 AS nvarchar(300)),cast(@mac_385 AS nvarchar(300)),cast(@mac_386 AS nvarchar(300)),cast(@mac_387 AS nvarchar(300)),cast(@mac_388 AS nvarchar(300)),cast(@mac_389 AS nvarchar(300)),cast(@mac_390 AS nvarchar(300)),cast(@mac_391 AS nvarchar(300)),cast(@mac_392 AS nvarchar(300)),cast(@mac_393 AS nvarchar(300)),cast(@mac_394 AS nvarchar(300)),cast(@mac_395 AS nvarchar(300)),cast(@mac_396 AS nvarchar(300)),cast(@mac_397 AS nvarchar(300)),cast(@mac_398 AS nvarchar(300)),cast(@mac_399 AS nvarchar(300)),cast(@mac_400 AS nvarchar(300)),cast(@mac_401 AS nvarchar(300)),cast(@mac_402 AS nvarchar(300)),cast(@mac_403 AS nvarchar(300)),cast(@mac_404 AS nvarchar(300)),cast(@mac_405 AS nvarchar(300)))
OR
a.Mac IN (cast(@mac_0 AS nvarchar(300)),cast(@mac_1 AS nvarchar(300)),cast(@mac_2 AS nvarchar(300)),cast(@mac_3 AS nvarchar(300)),cast(@mac_4 AS nvarchar(300)),cast(@mac_5 AS nvarchar(300)),cast(@mac_6 AS nvarchar(300)),cast(@mac_7 AS nvarchar(300)),cast(@mac_8 AS nvarchar(300)),cast(@mac_9 AS nvarchar(300)),cast(@mac_10 AS nvarchar(300)),cast(@mac_11 AS nvarchar(300)),cast(@mac_12 AS nvarchar(300)),cast(@mac_13 AS nvarchar(300)),cast(@mac_14 AS nvarchar(300)),cast(@mac_15 AS nvarchar(300)),cast(@mac_16 AS nvarchar(300)),cast(@mac_17 AS nvarchar(300)),cast(@mac_18 AS nvarchar(300)),cast(@mac_19 AS nvarchar(300)),cast(@mac_20 AS nvarchar(300)),cast(@mac_21 AS nvarchar(300)),cast(@mac_22 AS nvarchar(300)),cast(@mac_23 AS nvarchar(300)),cast(@mac_24 AS nvarchar(300)),cast(@mac_25 AS nvarchar(300)),cast(@mac_26 AS nvarchar(300)),cast(@mac_27 AS nvarchar(300)),cast(@mac_28 AS nvarchar(300)),cast(@mac_29 AS nvarchar(300)),cast(@mac_30 AS nvarchar(300)),cast(@mac_31 AS nvarchar(300)),cast(@mac_32 AS nvarchar(300)),cast(@mac_33 AS nvarchar(300)),cast(@mac_34 AS nvarchar(300)),cast(@mac_35 AS nvarchar(300)),cast(@mac_36 AS nvarchar(300)),cast(@mac_37 AS nvarchar(300)),cast(@mac_38 AS nvarchar(300)),cast(@mac_39 AS nvarchar(300)),cast(@mac_40 AS nvarchar(300)),cast(@mac_41 AS nvarchar(300)),cast(@mac_42 AS nvarchar(300)),cast(@mac_43 AS nvarchar(300)),cast(@mac_44 AS nvarchar(300)),cast(@mac_45 AS nvarchar(300)),cast(@mac_46 AS nvarchar(300)),cast(@mac_47 AS nvarchar(300)),cast(@mac_48 AS nvarchar(300)),cast(@mac_49 AS nvarchar(300)),cast(@mac_50 AS nvarchar(300)),cast(@mac_51 AS nvarchar(300)),cast(@mac_52 AS nvarchar(300)),cast(@mac_53 AS nvarchar(300)),cast(@mac_54 AS nvarchar(300)),cast(@mac_55 AS nvarchar(300)),cast(@mac_56 AS nvarchar(300)),cast(@mac_57 AS nvarchar(300)),cast(@mac_58 AS nvarchar(300)),cast(@mac_59 AS nvarchar(300)),cast(@mac_60 AS nvarchar(300)),cast(@mac_61 AS nvarchar(300)),cast(@mac_62 AS nvarchar(300)),cast(@mac_63 AS nvarchar(300)),cast(@mac_64 AS nvarchar(300)),cast(@mac_65 AS nvarchar(300)),cast(@mac_66 AS nvarchar(300)),cast(@mac_67 AS nvarchar(300)),cast(@mac_68 AS nvarchar(300)),cast(@mac_69 AS nvarchar(300)),cast(@mac_70 AS nvarchar(300)),cast(@mac_71 AS nvarchar(300)),cast(@mac_72 AS nvarchar(300)),cast(@mac_73 AS nvarchar(300)),cast(@mac_74 AS nvarchar(300)),cast(@mac_75 AS nvarchar(300)),cast(@mac_76 AS nvarchar(300)),cast(@mac_77 AS nvarchar(300)),cast(@mac_78 AS nvarchar(300)),cast(@mac_79 AS nvarchar(300)),cast(@mac_80 AS nvarchar(300)),cast(@mac_81 AS nvarchar(300)),cast(@mac_82 AS nvarchar(300)),cast(@mac_83 AS nvarchar(300)),cast(@mac_84 AS nvarchar(300)),cast(@mac_85 AS nvarchar(300)),cast(@mac_86 AS nvarchar(300)),cast(@mac_87 AS nvarchar(300)),cast(@mac_88 AS nvarchar(300)),cast(@mac_89 AS nvarchar(300)),cast(@mac_90 AS nvarchar(300)),cast(@mac_91 AS nvarchar(300)),cast(@mac_92 AS nvarchar(300)),cast(@mac_93 AS nvarchar(300)),cast(@mac_94 AS nvarchar(300)),cast(@mac_95 AS nvarchar(300)),cast(@mac_96 AS nvarchar(300)),cast(@mac_97 AS nvarchar(300)),cast(@mac_98 AS nvarchar(300)),cast(@mac_99 AS nvarchar(300)),cast(@mac_100 AS nvarchar(300)),cast(@mac_101 AS nvarchar(300)),cast(@mac_102 AS nvarchar(300)),cast(@mac_103 AS nvarchar(300)),cast(@mac_104 AS nvarchar(300)),cast(@mac_105 AS nvarchar(300)),cast(@mac_106 AS nvarchar(300)),cast(@mac_107 AS nvarchar(300)),cast(@mac_108 AS nvarchar(300)),cast(@mac_109 AS nvarchar(300)),cast(@mac_110 AS nvarchar(300)),cast(@mac_111 AS nvarchar(300)),cast(@mac_112 AS nvarchar(300)),cast(@mac_113 AS nvarchar(300)),cast(@mac_114 AS nvarchar(300)),cast(@mac_115 AS nvarchar(300)),cast(@mac_116 AS nvarchar(300)),cast(@mac_117 AS nvarchar(300)),cast(@mac_118 AS nvarchar(300)),cast(@mac_119 AS nvarchar(300)),cast(@mac_120 AS nvarchar(300)),cast(@mac_121 AS nvarchar(300)),cast(@mac_122 AS nvarchar(300)),cast(@mac_123 AS nvarchar(300)),cast(@mac_124 AS nvarchar(300)),cast(@mac_125 AS nvarchar(300)),cast(@mac_126 AS nvarchar(300)),cast(@mac_127 AS nvarchar(300)),cast(@mac_128 AS nvarchar(300)),cast(@mac_129 AS nvarchar(300)),cast(@mac_130 AS nvarchar(300)),cast(@mac_131 AS nvarchar(300)),cast(@mac_132 AS nvarchar(300)),cast(@mac_133 AS nvarchar(300)),cast(@mac_134 AS nvarchar(300)),cast(@mac_135 AS nvarchar(300)),cast(@mac_136 AS nvarchar(300)),cast(@mac_137 AS nvarchar(300)),cast(@mac_138 AS nvarchar(300)),cast(@mac_139 AS nvarchar(300)),cast(@mac_140 AS nvarchar(300)),cast(@mac_141 AS nvarchar(300)),cast(@mac_142 AS nvarchar(300)),cast(@mac_143 AS nvarchar(300)),cast(@mac_144 AS nvarchar(300)),cast(@mac_145 AS nvarchar(300)),cast(@mac_146 AS nvarchar(300)),cast(@mac_147 AS nvarchar(300)),cast(@mac_148 AS nvarchar(300)),cast(@mac_149 AS nvarchar(300)),cast(@mac_150 AS nvarchar(300)),cast(@mac_151 AS nvarchar(300)),cast(@mac_152 AS nvarchar(300)),cast(@mac_153 AS nvarchar(300)),cast(@mac_154 AS nvarchar(300)),cast(@mac_155 AS nvarchar(300)),cast(@mac_156 AS nvarchar(300)),cast(@mac_157 AS nvarchar(300)),cast(@mac_158 AS nvarchar(300)),cast(@mac_159 AS nvarchar(300)),cast(@mac_160 AS nvarchar(300)),cast(@mac_161 AS nvarchar(300)),cast(@mac_162 AS nvarchar(300)),cast(@mac_163 AS nvarchar(300)),cast(@mac_164 AS nvarchar(300)),cast(@mac_165 AS nvarchar(300)),cast(@mac_166 AS nvarchar(300)),cast(@mac_167 AS nvarchar(300)),cast(@mac_168 AS nvarchar(300)),cast(@mac_169 AS nvarchar(300)),cast(@mac_170 AS nvarchar(300)),cast(@mac_171 AS nvarchar(300)),cast(@mac_172 AS nvarchar(300)),cast(@mac_173 AS nvarchar(300)),cast(@mac_174 AS nvarchar(300)),cast(@mac_175 AS nvarchar(300)),cast(@mac_176 AS nvarchar(300)),cast(@mac_177 AS nvarchar(300)),cast(@mac_178 AS nvarchar(300)),cast(@mac_179 AS nvarchar(300)),cast(@mac_180 AS nvarchar(300)),cast(@mac_181 AS nvarchar(300)),cast(@mac_182 AS nvarchar(300)),cast(@mac_183 AS nvarchar(300)),cast(@mac_184 AS nvarchar(300)),cast(@mac_185 AS nvarchar(300)),cast(@mac_186 AS nvarchar(300)),cast(@mac_187 AS nvarchar(300)),cast(@mac_188 AS nvarchar(300)),cast(@mac_189 AS nvarchar(300)),cast(@mac_190 AS nvarchar(300)),cast(@mac_191 AS nvarchar(300)),cast(@mac_192 AS nvarchar(300)),cast(@mac_193 AS nvarchar(300)),cast(@mac_194 AS nvarchar(300)),cast(@mac_195 AS nvarchar(300)),cast(@mac_196 AS nvarchar(300)),cast(@mac_197 AS nvarchar(300)),cast(@mac_198 AS nvarchar(300)),cast(@mac_199 AS nvarchar(300)),cast(@mac_200 AS nvarchar(300)),cast(@mac_201 AS nvarchar(300)),cast(@mac_202 AS nvarchar(300)),cast(@mac_203 AS nvarchar(300)),cast(@mac_204 AS nvarchar(300)),cast(@mac_205 AS nvarchar(300)),cast(@mac_206 AS nvarchar(300)),cast(@mac_207 AS nvarchar(300)),cast(@mac_208 AS nvarchar(300)),cast(@mac_209 AS nvarchar(300)),cast(@mac_210 AS nvarchar(300)),cast(@mac_211 AS nvarchar(300)),cast(@mac_212 AS nvarchar(300)),cast(@mac_213 AS nvarchar(300)),cast(@mac_214 AS nvarchar(300)),cast(@mac_215 AS nvarchar(300)),cast(@mac_216 AS nvarchar(300)),cast(@mac_217 AS nvarchar(300)),cast(@mac_218 AS nvarchar(300)),cast(@mac_219 AS nvarchar(300)),cast(@mac_220 AS nvarchar(300)),cast(@mac_221 AS nvarchar(300)),cast(@mac_222 AS nvarchar(300)),cast(@mac_223 AS nvarchar(300)),cast(@mac_224 AS nvarchar(300)),cast(@mac_225 AS nvarchar(300)),cast(@mac_226 AS nvarchar(300)),cast(@mac_227 AS nvarchar(300)),cast(@mac_228 AS nvarchar(300)),cast(@mac_229 AS nvarchar(300)),cast(@mac_230 AS nvarchar(300)),cast(@mac_231 AS nvarchar(300)),cast(@mac_232 AS nvarchar(300)),cast(@mac_233 AS nvarchar(300)),cast(@mac_234 AS nvarchar(300)),cast(@mac_235 AS nvarchar(300)),cast(@mac_236 AS nvarchar(300)),cast(@mac_237 AS nvarchar(300)),cast(@mac_238 AS nvarchar(300)),cast(@mac_239 AS nvarchar(300)),cast(@mac_240 AS nvarchar(300)),cast(@mac_241 AS nvarchar(300)),cast(@mac_242 AS nvarchar(300)),cast(@mac_243 AS nvarchar(300)),cast(@mac_244 AS nvarchar(300)),cast(@mac_245 AS nvarchar(300)),cast(@mac_246 AS nvarchar(300)),cast(@mac_247 AS nvarchar(300)),cast(@mac_248 AS nvarchar(300)),cast(@mac_249 AS nvarchar(300)),cast(@mac_250 AS nvarchar(300)),cast(@mac_251 AS nvarchar(300)),cast(@mac_252 AS nvarchar(300)),cast(@mac_253 AS nvarchar(300)),cast(@mac_254 AS nvarchar(300)),cast(@mac_255 AS nvarchar(300)),cast(@mac_256 AS nvarchar(300)),cast(@mac_257 AS nvarchar(300)),cast(@mac_258 AS nvarchar(300)),cast(@mac_259 AS nvarchar(300)),cast(@mac_260 AS nvarchar(300)),cast(@mac_261 AS nvarchar(300)),cast(@mac_262 AS nvarchar(300)),cast(@mac_263 AS nvarchar(300)),cast(@mac_264 AS nvarchar(300)),cast(@mac_265 AS nvarchar(300)),cast(@mac_266 AS nvarchar(300)),cast(@mac_267 AS nvarchar(300)),cast(@mac_268 AS nvarchar(300)),cast(@mac_269 AS nvarchar(300)),cast(@mac_270 AS nvarchar(300)),cast(@mac_271 AS nvarchar(300)),cast(@mac_272 AS nvarchar(300)),cast(@mac_273 AS nvarchar(300)),cast(@mac_274 AS nvarchar(300)),cast(@mac_275 AS nvarchar(300)),cast(@mac_276 AS nvarchar(300)),cast(@mac_277 AS nvarchar(300)),cast(@mac_278 AS nvarchar(300)),cast(@mac_279 AS nvarchar(300)),cast(@mac_280 AS nvarchar(300)),cast(@mac_281 AS nvarchar(300)),cast(@mac_282 AS nvarchar(300)),cast(@mac_283 AS nvarchar(300)),cast(@mac_284 AS nvarchar(300)),cast(@mac_285 AS nvarchar(300)),cast(@mac_286 AS nvarchar(300)),cast(@mac_287 AS nvarchar(300)),cast(@mac_288 AS nvarchar(300)),cast(@mac_289 AS nvarchar(300)),cast(@mac_290 AS nvarchar(300)),cast(@mac_291 AS nvarchar(300)),cast(@mac_292 AS nvarchar(300)),cast(@mac_293 AS nvarchar(300)),cast(@mac_294 AS nvarchar(300)),cast(@mac_295 AS nvarchar(300)),cast(@mac_296 AS nvarchar(300)),cast(@mac_297 AS nvarchar(300)),cast(@mac_298 AS nvarchar(300)),cast(@mac_299 AS nvarchar(300)),cast(@mac_300 AS nvarchar(300)),cast(@mac_301 AS nvarchar(300)),cast(@mac_302 AS nvarchar(300)),cast(@mac_303 AS nvarchar(300)),cast(@mac_304 AS nvarchar(300)),cast(@mac_305 AS nvarchar(300)),cast(@mac_306 AS nvarchar(300)),cast(@mac_307 AS nvarchar(300)),cast(@mac_308 AS nvarchar(300)),cast(@mac_309 AS nvarchar(300)),cast(@mac_310 AS nvarchar(300)),cast(@mac_311 AS nvarchar(300)),cast(@mac_312 AS nvarchar(300)),cast(@mac_313 AS nvarchar(300)),cast(@mac_314 AS nvarchar(300)),cast(@mac_315 AS nvarchar(300)),cast(@mac_316 AS nvarchar(300)),cast(@mac_317 AS nvarchar(300)),cast(@mac_318 AS nvarchar(300)),cast(@mac_319 AS nvarchar(300)),cast(@mac_320 AS nvarchar(300)),cast(@mac_321 AS nvarchar(300)),cast(@mac_322 AS nvarchar(300)),cast(@mac_323 AS nvarchar(300)),cast(@mac_324 AS nvarchar(300)),cast(@mac_325 AS nvarchar(300)),cast(@mac_326 AS nvarchar(300)),cast(@mac_327 AS nvarchar(300)),cast(@mac_328 AS nvarchar(300)),cast(@mac_329 AS nvarchar(300)),cast(@mac_330 AS nvarchar(300)),cast(@mac_331 AS nvarchar(300)),cast(@mac_332 AS nvarchar(300)),cast(@mac_333 AS nvarchar(300)),cast(@mac_334 AS nvarchar(300)),cast(@mac_335 AS nvarchar(300)),cast(@mac_336 AS nvarchar(300)),cast(@mac_337 AS nvarchar(300)),cast(@mac_338 AS nvarchar(300)),cast(@mac_339 AS nvarchar(300)),cast(@mac_340 AS nvarchar(300)),cast(@mac_341 AS nvarchar(300)),cast(@mac_342 AS nvarchar(300)),cast(@mac_343 AS nvarchar(300)),cast(@mac_344 AS nvarchar(300)),cast(@mac_345 AS nvarchar(300)),cast(@mac_346 AS nvarchar(300)),cast(@mac_347 AS nvarchar(300)),cast(@mac_348 AS nvarchar(300)),cast(@mac_349 AS nvarchar(300)),cast(@mac_350 AS nvarchar(300)),cast(@mac_351 AS nvarchar(300)),cast(@mac_352 AS nvarchar(300)),cast(@mac_353 AS nvarchar(300)),cast(@mac_354 AS nvarchar(300)),cast(@mac_355 AS nvarchar(300)),cast(@mac_356 AS nvarchar(300)),cast(@mac_357 AS nvarchar(300)),cast(@mac_358 AS nvarchar(300)),cast(@mac_359 AS nvarchar(300)),cast(@mac_360 AS nvarchar(300)),cast(@mac_361 AS nvarchar(300)),cast(@mac_362 AS nvarchar(300)),cast(@mac_363 AS nvarchar(300)),cast(@mac_364 AS nvarchar(300)),cast(@mac_365 AS nvarchar(300)),cast(@mac_366 AS nvarchar(300)),cast(@mac_367 AS nvarchar(300)),cast(@mac_368 AS nvarchar(300)),cast(@mac_369 AS nvarchar(300)),cast(@mac_370 AS nvarchar(300)),cast(@mac_371 AS nvarchar(300)),cast(@mac_372 AS nvarchar(300)),cast(@mac_373 AS nvarchar(300)),cast(@mac_374 AS nvarchar(300)),cast(@mac_375 AS nvarchar(300)),cast(@mac_376 AS nvarchar(300)),cast(@mac_377 AS nvarchar(300)),cast(@mac_378 AS nvarchar(300)),cast(@mac_379 AS nvarchar(300)),cast(@mac_380 AS nvarchar(300)),cast(@mac_381 AS nvarchar(300)),cast(@mac_382 AS nvarchar(300)),cast(@mac_383 AS nvarchar(300)),cast(@mac_384 AS nvarchar(300)),cast(@mac_385 AS nvarchar(300)),cast(@mac_386 AS nvarchar(300)),cast(@mac_387 AS nvarchar(300)),cast(@mac_388 AS nvarchar(300)),cast(@mac_389 AS nvarchar(300)),cast(@mac_390 AS nvarchar(300)),cast(@mac_391 AS nvarchar(300)),cast(@mac_392 AS nvarchar(300)),cast(@mac_393 AS nvarchar(300)),cast(@mac_394 AS nvarchar(300)),cast(@mac_395 AS nvarchar(300)),cast(@mac_396 AS nvarchar(300)),cast(@mac_397 AS nvarchar(300)),cast(@mac_398 AS nvarchar(300)),cast(@mac_399 AS nvarchar(300)),cast(@mac_400 AS nvarchar(300)),cast(@mac_401 AS nvarchar(300)),cast(@mac_402 AS nvarchar(300)),cast(@mac_403 AS nvarchar(300)),cast(@mac_404 AS nvarchar(300)),cast(@mac_405 AS nvarchar(300)))
)

[Hemoco · Accepted solution]

This query has now been optimized for SQL Server 2005 and higher. The modified query is included in Lansweeper 5.1.0.63, which is now available for download: http://lansweeper.com/Download.aspx

[sukaitsu]

Results have been emailed.

[Hemoco]

Can you create a custom report using the code below and send the results to support@lansweeper.com

SELECT
  t.name tablename,
  a.rows,
  b.name type,
  c.*
FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.object_id = c.object_id
  INNER JOIN sys.types b ON c.system_type_id = b.system_type_id
  INNER JOIN (
    SELECT i.object_id, MAX(p.rows) rows
    FROM sys.tables t
      INNER JOIN sys.indexes i ON t.object_id = i.object_id
      INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    GROUP BY i.object_id
    ) a ON a.object_id = t.object_id
ORDER BY tablename, c.column_id

[Hemoco]

We'll have development take a look at this query. They should respond sometime next week.