{"id":471627,"date":"2025-08-22T15:00:55","date_gmt":"2025-08-22T15:00:55","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=471627"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=471627","title":{"rendered":"<span>\u0421\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044f \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u044f \u0431\u0430\u0437 \u2014 VLDB \u041f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446<\/span>"},"content":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p>\u0414\u0430\u043d\u043d\u044b\u0439 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u0441\u043e\u0431\u043e\u0439 \u0441\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044e \u043f\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0442\u0430\u0431\u043b\u0438\u0446 \u0432 \u043a\u0440\u0443\u043f\u043d\u044b\u0445 \u0431\u0430\u0437\u0430\u0445 \u0434\u0430\u043d\u043d\u044b\u0445 (VLDB), \u043d\u0430\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u043d\u0443\u044e \u043d\u0430 \u043e\u0431\u0435\u0441\u043f\u0435\u0447\u0435\u043d\u0438\u0435 \u0438\u0445 \u0441\u0442\u0430\u0431\u0438\u043b\u044c\u043d\u043e\u0439 \u0440\u0430\u0431\u043e\u0442\u044b, \u0432\u044b\u0441\u043e\u043a\u043e\u0439 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0438 \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0432.<\/p>\n<p>\u0414\u0430\u043d\u043d\u0430\u044f \u0441\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044f \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0430\u043d\u0430 \u0441 \u0443\u0447\u0435\u0442\u043e\u043c \u0441\u043f\u0435\u0446\u0438\u0444\u0438\u043a\u0438 \u0440\u0430\u0431\u043e\u0442\u044b \u0441 \u0431\u043e\u043b\u044c\u0448\u0438\u043c\u0438 \u043e\u0431\u044a\u0435\u043c\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u043e\u0440\u0438\u0435\u043d\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0430 \u043d\u0430 \u043c\u0438\u043d\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044e \u043f\u0440\u043e\u0441\u0442\u043e\u0435\u0432 \u0441\u0438\u0441\u0442\u0435\u043c\u044b \u043f\u0440\u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0438 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u044e\u0449\u0438\u0445 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439.<\/p>\n<h2>\u041f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f<\/h2>\n<p>\u041e\u0441\u043d\u043e\u0432\u043d\u0430\u044f \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0430 \u043f\u0440\u0438 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0438 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 (VLDB) \u0437\u0430\u043a\u043b\u044e\u0447\u0430\u0435\u0442\u0441\u044f \u0432 \u0442\u043e\u043c, \u0447\u0442\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f \u0432\u0441\u0435\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u043c\u043e\u0436\u0435\u0442 \u0437\u0430\u043d\u0438\u043c\u0430\u0442\u044c \u043e\u0447\u0435\u043d\u044c \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0435 \u0432\u0440\u0435\u043c\u044f, \u0438\u043d\u043e\u0433\u0434\u0430 \u0440\u0430\u0441\u0442\u044f\u0433\u0438\u0432\u0430\u044f\u0441\u044c \u043d\u0430 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0434\u043d\u0435\u0439. \u042d\u0442\u043e \u0441\u043e\u0437\u0434\u0430\u0435\u0442 \u0441\u0435\u0440\u044c\u0435\u0437\u043d\u044b\u0435 \u0442\u0440\u0443\u0434\u043d\u043e\u0441\u0442\u0438 \u0434\u043b\u044f \u043e\u0431\u0435\u0441\u043f\u0435\u0447\u0435\u043d\u0438\u044f \u043d\u0435\u043f\u0440\u0435\u0440\u044b\u0432\u043d\u043e\u0441\u0442\u0438 \u0431\u0438\u0437\u043d\u0435\u0441-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u043e\u0432 \u0438 \u0442\u0440\u0435\u0431\u0443\u0435\u0442 \u0441\u043f\u0435\u0446\u0438\u0430\u043b\u044c\u043d\u044b\u0445 \u043f\u043e\u0434\u0445\u043e\u0434\u043e\u0432 \u043a \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0434\u0430\u043d\u043d\u043e\u0433\u043e \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430.<\/p>\n<h3>\u041f\u0440\u043e\u0431\u043b\u0435\u043c\u044b \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u043e\u0439 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438<\/h3>\n<p>\u041f\u0440\u0438 \u0440\u0430\u0431\u043e\u0442\u0435 \u0441 \u043a\u0440\u0443\u043f\u043d\u044b\u043c\u0438 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u044b\u0435 \u043f\u043e\u0434\u0445\u043e\u0434\u044b \u043a \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0441\u0442\u0430\u043b\u043a\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0441\u043e \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u043c\u0438 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0430\u043c\u0438:<\/p>\n<ul>\n<li>\n<p><strong>\u0414\u043b\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0435 \u0432\u0440\u0435\u043c\u044f \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f:<\/strong> \u043f\u043e\u043b\u043d\u0430\u044f \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f \u0432\u0441\u0435\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u043c\u043e\u0436\u0435\u0442 \u0437\u0430\u043d\u0438\u043c\u0430\u0442\u044c \u0434\u043d\u0438, \u0447\u0442\u043e \u043d\u0435\u043f\u0440\u0438\u0435\u043c\u043b\u0435\u043c\u043e \u0434\u043b\u044f \u0441\u0438\u0441\u0442\u0435\u043c \u0441 \u0442\u0440\u0435\u0431\u043e\u0432\u0430\u043d\u0438\u044f\u043c\u0438 \u0432\u044b\u0441\u043e\u043a\u043e\u0439 \u0434\u043e\u0441\u0442\u0443\u043f\u043d\u043e\u0441\u0442\u0438.<\/p>\n<\/li>\n<li>\n<p><strong>\u0412\u044b\u0441\u043e\u043a\u0430\u044f \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0430 \u043d\u0430 \u0441\u0438\u0441\u0442\u0435\u043c\u0443:<\/strong> \u043f\u0440\u043e\u0446\u0435\u0441\u0441 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u044f\u0435\u0442 \u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0440\u0435\u0441\u0443\u0440\u0441\u044b \u0441\u0435\u0440\u0432\u0435\u0440\u0430, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u043d\u0435\u0433\u0430\u0442\u0438\u0432\u043d\u043e \u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0442\u044c\u0441\u044f \u043d\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043e\u0441\u043d\u043e\u0432\u043d\u044b\u0445 \u0440\u0430\u0431\u043e\u0447\u0438\u0445 \u043d\u0430\u0433\u0440\u0443\u0437\u043e\u043a.<\/p>\n<\/li>\n<li>\n<p><strong>\u0411\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043a\u0438:<\/strong> \u043c\u043d\u043e\u0433\u0438\u0435 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0438 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0442\u0440\u0435\u0431\u0443\u044e\u0442 \u0443\u0441\u0442\u0430\u043d\u043e\u0432\u043a\u0438 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043e\u043a, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442\u044c \u043a \u043f\u0440\u043e\u0441\u0442\u043e\u044f\u043c \u0432 \u0440\u0430\u0431\u043e\u0442\u0435 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439.<\/p>\n<\/li>\n<\/ul>\n<h3>\u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438<\/h3>\n<p>\u0414\u043b\u044f \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u0443\u043a\u0430\u0437\u0430\u043d\u043d\u044b\u0445 \u043f\u0440\u043e\u0431\u043b\u0435\u043c \u043f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u0435\u0442\u0441\u044f \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434:<\/p>\n<p>\u0412\u043c\u0435\u0441\u0442\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0432\u0441\u0435\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0440\u0430\u0437\u0434\u0435\u043b\u0438\u0442\u044c \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u043d\u0430 2 \u0433\u0440\u0443\u043f\u043f\u044b:<\/p>\n<ul>\n<li>\n<p><strong>\u0413\u043e\u0440\u044f\u0447\u0438\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b<\/strong>: <\/p>\n<ul>\n<li>\n<p>\u0412\u044b\u0441\u043e\u043a\u0430\u044f \u0447\u0430\u0441\u0442\u043e\u0442\u0430 \u043e\u0431\u0440\u0430\u0449\u0435\u043d\u0438\u0439 (\u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432) \u043a \u0442\u0430\u0431\u043b\u0438\u0446\u0435.<\/p>\n<\/li>\n<li>\n<p>\u0427\u0430\u0441\u0442\u044b\u0435 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0434\u0430\u043d\u043d\u044b\u0445 (INSERT \/ UPDATE \/ DELETE).<\/p>\n<\/li>\n<li>\n<p>\u0423\u0447\u0430\u0441\u0442\u0438\u0435 \u0432 \u043d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u0432\u0430\u0436\u043d\u044b\u0445 \u0431\u0438\u0437\u043d\u0435\u0441-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430\u0445 \u0438\u043b\u0438 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044f\u0445.<\/p>\n<\/li>\n<li>\n<p>\u041a\u0440\u0438\u0442\u0438\u0447\u043d\u043e\u0441\u0442\u044c \u0434\u043b\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0441\u0438\u0441\u0442\u0435\u043c\u044b.<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<li>\n<p><strong>\u0422\u0435\u043f\u043b\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b:<\/strong> <\/p>\n<ul>\n<li>\n<p>\u0418\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044e\u0442\u0441\u044f \u0443\u043c\u0435\u0440\u0435\u043d\u043d\u043e.<\/p>\n<\/li>\n<li>\n<p>\u041c\u043e\u0433\u0443\u0442 \u0431\u044b\u0442\u044c \u0447\u0430\u0441\u0442\u044c\u044e \u043f\u0440\u043e\u043c\u0435\u0436\u0443\u0442\u043e\u0447\u043d\u044b\u0445 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u043e\u0432 \u0438\u043b\u0438 \u043e\u0442\u0447\u0435\u0442\u043d\u043e\u0441\u0442\u0438.<\/p>\n<\/li>\n<li>\n<p>\u041d\u0435 \u0442\u0430\u043a \u043a\u0440\u0438\u0442\u0438\u0447\u043d\u044b \u0434\u043b\u044f \u0435\u0436\u0435\u0434\u043d\u0435\u0432\u043d\u043e\u0439 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u043e\u043d\u043d\u043e\u0439 \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0438.<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h4>\u041e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u0435 \u043a\u0430\u0442\u0435\u0433\u043e\u0440\u0438\u0439 \u0442\u0430\u0431\u043b\u0438\u0446<\/h4>\n<p>\u0421\u0443\u0449\u0435\u0441\u0442\u0432\u0443\u0435\u0442 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u043c\u0435\u0442\u043e\u0434\u043e\u0432 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u044f \u0447\u0430\u0441\u0442\u043e\u0442\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446:<\/p>\n<ul>\n<li>\n<p><strong>\u0410\u043d\u0430\u043b\u0438\u0437 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432<\/strong><\/p>\n<\/li>\n<li>\n<p><strong>\u0418\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 Extended Events<\/strong><\/p>\n<\/li>\n<li>\n<p><strong>\u0410\u043d\u0430\u043b\u0438\u0437 \u043a\u044d\u0448\u0430 \u043f\u043b\u0430\u043d\u043e\u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432<\/strong><\/p>\n<\/li>\n<li>\n<p><strong>\u041f\u0440\u0438\u043c\u0435\u043d\u0435\u043d\u0438\u0435 SQL Default Trace<\/strong><\/p>\n<\/li>\n<li>\n<p><strong>\u0410\u043d\u0430\u043b\u0438\u0437 \u0442\u0440\u0435\u0439\u0441\u043e\u0432 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e SQL Profiler<\/strong><\/p>\n<\/li>\n<\/ul>\n<p>\u0412 \u043d\u0430\u0448\u0435\u043c \u0441\u043b\u0443\u0447\u0430\u0435 \u043c\u044b \u0432\u044b\u0431\u0440\u0430\u043b\u0438 \u043c\u0435\u0442\u043e\u0434 \u0430\u043d\u0430\u043b\u0438\u0437\u0430 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432, \u043f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u043e\u043d \u043c\u0435\u043d\u0435\u0435 \u0442\u0440\u0443\u0434\u043e\u0451\u043c\u043a\u0438\u0439 \u0438 \u043c\u0438\u043d\u0438\u043c\u0430\u043b\u044c\u043d\u043e \u0432\u043b\u0438\u044f\u0435\u0442 \u043d\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b. \u0412\u0430\u0436\u043d\u043e \u043e\u0442\u043c\u0435\u0442\u0438\u0442\u044c, \u0447\u0442\u043e \u043b\u044e\u0431\u043e\u0439 \u0441\u0431\u043e\u0440 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0434\u0430\u043d\u043d\u044b\u0445 \u0441\u043e\u0437\u0434\u0430\u0451\u0442 \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u0443\u044e \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0443 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440. \u041a\u043b\u044e\u0447\u0435\u0432\u043e\u0439 \u0432\u043e\u043f\u0440\u043e\u0441 \u0441\u043e\u0441\u0442\u043e\u0438\u0442 \u0432 \u043e\u0431\u044a\u0451\u043c\u0435 \u0438 \u0445\u0430\u0440\u0430\u043a\u0442\u0435\u0440\u0435 \u044d\u0442\u043e\u0439 \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0438, \u0430 \u0442\u0430\u043a\u0436\u0435 \u0435\u0451 \u0432\u043b\u0438\u044f\u043d\u0438\u0438 \u043d\u0430 \u043e\u0431\u0449\u0443\u044e \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b.<\/p>\n<pre><code class=\"sql\">SELECT      OBJECT_NAME(ius.object_id) AS TableName,     i.name AS IndexName,     i.type_desc AS IndexType,     ius.user_seeks + ius.user_scans + ius.user_lookups AS TotalUserReads,     ius.user_updates AS TotalUserWrites,     ius.last_user_seek,     ius.last_user_scan,     ius.last_user_lookup FROM      sys.dm_db_index_usage_stats ius INNER JOIN      sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id WHERE      OBJECTPROPERTY(ius.object_id, 'IsMsShipped') = 0 -- \u0438\u0441\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u043d\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b     AND ius.database_id = DB_ID() -- \u043f\u0440\u043e\u0432\u0435\u0440\u0438\u043c \u0442\u043e\u043b\u044c\u043a\u043e \u0442\u043a\u0443\u0449\u0443\u044e \u0411\u0414 ORDER BY      TotalUserReads DESC;<\/code><\/pre>\n<h4>\u041c\u0430\u0440\u043a\u0438\u0440\u043e\u0432\u043a\u0430 \u0442\u0430\u0431\u043b\u0438\u0446<\/h4>\n<p>\u041f\u043e\u0441\u043b\u0435 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u044f <strong>\u0433\u043e\u0440\u044f\u0447\u0438\u0445 \u0442\u0430\u0431\u043b\u0438\u0446<\/strong>, \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u043e\u0442\u043c\u0435\u0442\u0438\u0442\u044c \u0438\u0445 \u0434\u043b\u044f \u0434\u0430\u043b\u044c\u043d\u0435\u0439\u0448\u0435\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0438. \u0412 \u043d\u0430\u0448\u0435\u043c \u0441\u043b\u0443\u0447\u0430\u0435 \u0440\u0435\u0448\u0435\u043d\u043e \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c \u0440\u0430\u0441\u0448\u0438\u0440\u0435\u043d\u043d\u043e\u0435 \u0441\u0432\u043e\u0439\u0441\u0442\u0432\u043e <strong>&#171;IsHotTable&#187;<\/strong><\/p>\n<p>\u0414\u043b\u044f \u0443\u0434\u043e\u0431\u0441\u0442\u0432\u0430 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f, \u044f \u043e\u0444\u043e\u0440\u043c\u0438\u043b \u043a\u043e\u0434 \u0432 \u0432\u0438\u0434\u0435 \u0445\u0440\u0430\u043d\u0438\u043c\u043e\u0439 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u044b<\/p>\n<blockquote>\n<p>\u2705 \u0414\u043b\u044f \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0440\u0430\u0431\u043e\u0442\u044b \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0443\u0435\u0442\u0441\u044f \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u0443\u044e \u0441\u0445\u0435\u043c\u0443 \u0432 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445, \u0432 \u0440\u0430\u043c\u043a\u0430\u0445 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u0431\u0443\u0434\u0443\u0442 \u0440\u0430\u0437\u043c\u0435\u0449\u0430\u0442\u044c\u0441\u044f \u0432\u0441\u0435 \u043e\u0431\u044a\u0435\u043a\u0442\u044b \u0434\u043b\u044f \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u044f \u0411\u0414, \u0442\u0430\u043a\u0438\u0435 \u043a\u0430\u043a \u0445\u0440\u0430\u043d\u0438\u043c\u044b\u0435 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u044b \u0438\u043b\u0438 \u0441\u043b\u0443\u0436\u0435\u0431\u043d\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b.<\/p>\n<\/blockquote>\n<pre><code class=\"sql\">IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'DBA') BEGIN     EXEC('CREATE SCHEMA DBA');     PRINT 'Schema [DBA] created successfully.'; END ELSE BEGIN     PRINT 'Schema [DBA] already exists.'; END<\/code><\/pre>\n<pre><code class=\"sql\">CREATE PROCEDURE dba.SetTableExtendedProperty     @TableName SYSNAME,     @SchemaName SYSNAME = 'dbo',     @PropertyName SYSNAME,     @PropertyValue NVARCHAR(4000) AS BEGIN     SET NOCOUNT ON;      DECLARE @SQL NVARCHAR(MAX);      -- \u041f\u0440\u043e\u0432\u0435\u0440\u0438\u0442\u044c \u043d\u0430\u043b\u0438\u0447\u0438\u0435 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u0430     IF EXISTS (         SELECT 1         FROM fn_listextendedproperty(@PropertyName, 'SCHEMA', @SchemaName, 'TABLE', @TableName, NULL, NULL)     )     BEGIN         -- \u0415\u0441\u043b\u0438 \u0438\u043c\u0435\u0435\u0442\u0441\u044f,  \u0442\u043e\u0433\u0434\u0430 \u043d\u0443\u0436\u043d\u043e \u0443\u0434\u0430\u043b\u0438\u0442\u044c \u0438 \u043f\u0435\u0440\u0435\u0441\u043e\u0437\u0434\u0430\u0442\u044c          SET @SQL = '         EXEC sp_dropextendedproperty              @name = N''' + @PropertyName + ''',              @level0type = N''SCHEMA'',              @level0name = N''' + @SchemaName + ''',              @level1type = N''TABLE'',              @level1name = N''' + @TableName + ''';';          EXEC sp_executesql @SQL;     END      -- \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043d\u043e\u0432\u044b\u0439     SET @SQL = '     EXEC sp_addextendedproperty          @name = N''' + @PropertyName + ''',          @value = N''' + REPLACE(@PropertyValue, '''', '''''') + ''',          @level0type = N''SCHEMA'',          @level0name = N''' + @SchemaName + ''',          @level1type = N''TABLE'',          @level1name = N''' + @TableName + ''';';      EXEC sp_executesql @SQL;      PRINT 'Extended property [' + @PropertyName + '] updated for [' + @SchemaName + '].[' + @TableName + ']'; END;<\/code><\/pre>\n<blockquote>\n<p>\u261d\ud83c\udffc\u0412\u0430\u0436\u043d\u043e \u043f\u043e\u043c\u043d\u0438\u0442\u044c, \u0447\u0442\u043e &#171;\u0433\u043e\u0440\u044f\u0447\u0438\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b&#187; \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u044f\u044e\u0442\u0441\u044f \u043d\u0435 \u0442\u043e\u043b\u044c\u043a\u043e \u043f\u043e \u0447\u0430\u0441\u0442\u043e\u0442\u0435 \u043e\u0431\u0440\u0430\u0449\u0435\u043d\u0438\u0439, \u043d\u043e \u0438 \u043f\u043e \u0438\u0445 \u0432\u0430\u0436\u043d\u043e\u0441\u0442\u0438 \u0432 \u0431\u0438\u0437\u043d\u0435\u0441-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430\u0445. \u0420\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0443\u0435\u0442\u0441\u044f \u0441\u043e\u0441\u0442\u0430\u0432\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0442\u0430\u043a\u0438\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u0432\u0440\u0443\u0447\u043d\u0443\u044e, \u043f\u043e\u0441\u043b\u0435 \u0447\u0435\u0433\u043e \u043f\u0440\u043e\u0438\u0437\u0432\u0435\u0441\u0442\u0438 \u0438\u0445 \u043c\u0430\u0440\u043a\u0438\u0440\u043e\u0432\u043a\u0443 \u0432 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445.<\/p>\n<\/blockquote>\n<p>\u041f\u0440\u0438\u043c\u0435\u0440 \u043c\u0430\u0440\u043a\u0438\u0440\u043e\u0432\u043a\u0438<\/p>\n<pre><code class=\"sql\">EXEC dba.SetTableExtendedProperty     @TableName = 'Customers',     @SchemaName = 'dbo',     @PropertyName = 'IsHotTable',     @PropertyValue = '1';<\/code><\/pre>\n<p><strong>\u041f\u0440\u0438\u043c\u0435\u0440<\/strong>: \u041a\u0430\u043a \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0441 \u043c\u0430\u0440\u043a\u0438\u0440\u043e\u0432\u043a\u043e\u0439 <strong>IsHotTable<\/strong><\/p>\n<pre><code class=\"sql\">SELECT t.name   AS TableName,        ep.name  AS PropertyName,        ep.value AS PropertyValue FROM sys.tables t          CROSS APPLY      fn_listextendedproperty(default, 'SCHEMA', SCHEMA_NAME(t.schema_id), 'TABLE', t.name, NULL, NULL) ep WHERE t.is_ms_shipped = 0   and ep.name = 'IsHotTable'   and ep.value = '1';<\/code><\/pre>\n<h4>\u041e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435 \u0433\u043e\u0440\u044f\u0447\u0438\u0445 \u0442\u0430\u0431\u043b\u0438\u0446<\/h4>\n<p>\u0412 \u0440\u0430\u043c\u043a\u0430\u0445 \u043d\u0430\u0448\u0435\u0439 \u0441\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u0438 \u043f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u0435\u0442\u0441\u044f \u0435\u0436\u0435\u0434\u043d\u0435\u0432\u043d\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0439 \u0432\u0438\u0434 \u0442\u0430\u0431\u043b\u0438\u0446, \u043d\u0435\u0441\u043c\u043e\u0442\u0440\u044f \u043d\u0430 \u0443\u0440\u043e\u0432\u043d\u0438 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438.<\/p>\n<p>\u0414\u043b\u044f \u0441\u043e\u043a\u0440\u0430\u0449\u0435\u043d\u0438\u044f \u0432\u0440\u0435\u043c\u0435\u043d\u0438 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0431\u0443\u0434\u0435\u043c \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u043c\u043d\u043e\u0433\u043e\u043f\u043e\u0442\u043e\u0447\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434. \u0422\u0430\u0431\u043b\u0438\u0446\u044b \u0431\u0443\u0434\u0443\u0442 \u0440\u0430\u0437\u0434\u0435\u043b\u0435\u043d\u044b \u043d\u0430 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0433\u0440\u0443\u043f\u043f \u0432 \u0437\u0430\u0432\u0438\u0441\u0438\u043c\u043e\u0441\u0442\u0438 \u043e\u0442 \u0434\u043e\u0441\u0442\u0443\u043f\u043d\u044b\u0445 \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0432 \u0441\u0438\u0441\u0442\u0435\u043c\u044b. \u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0433\u0440\u0443\u043f\u043f \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u043e \u0432 \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0438\u0438 \u0441 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e \u0441\u0435\u0440\u0432\u0435\u0440\u0430.<\/p>\n<p><strong>\u041e\u043f\u0438\u0441\u0430\u043d\u0438\u0435 \u0441\u0438\u0441\u0442\u0435\u043c\u044b \u043c\u043d\u043e\u0433\u043e\u043f\u043e\u0442\u043e\u0447\u043d\u043e\u0433\u043e \u043f\u043e\u0434\u0445\u043e\u0434\u0430<\/strong><\/p>\n<p>\u0421\u0438\u0441\u0442\u0435\u043c\u0430 \u0440\u0435\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u043d\u0430 \u0441 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435\u043c \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u043e\u0433\u043e \u043c\u0435\u0445\u0430\u043d\u0438\u0437\u043c\u0430 \u0421\u0423\u0411\u0414 &#8212; SQL Agent Job, \u0432 \u0440\u0430\u043c\u043a\u0430\u0445 \u043a\u043e\u0442\u043e\u0440\u043e\u0433\u043e \u0441\u043e\u0437\u0434\u0430\u0435\u0442\u0441\u044f \u0433\u043b\u0430\u0432\u043d\u043e\u0435 \u0437\u0430\u0434\u0430\u043d\u0438\u0435 (Master Job), \u043a\u043e\u0442\u043e\u0440\u043e\u0435 \u043e\u0442\u0432\u0435\u0447\u0430\u0435\u0442 \u0437\u0430 \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u0438 \u0443\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u0435 \u043f\u043e\u0434\u0437\u0430\u0434\u0430\u043d\u0438\u044f\u043c\u0438 (\u043f\u043e\u0442\u043e\u043a\u0430\u043c\u0438).<\/p>\n<p>Master Job \u0441\u043e\u0441\u0442\u043e\u0438\u0442 \u0438\u0437 2 \u0448\u0430\u0433\u043e\u0432:<\/p>\n<ul>\n<li>\n<p>\u0428\u0430\u0433 \u21161 &#8212; \u0421\u043e\u0437\u0434\u0430\u0435\u0442 \u0434\u0438\u043d\u0430\u043c\u0438\u0447\u0435\u0441\u043a\u0438\u0435 \u043f\u043e\u0434\u0437\u0430\u0434\u0430\u043d\u0438\u044f \u0432 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0435, \u0440\u0430\u0432\u043d\u043e\u043c \u0447\u0438\u0441\u043b\u0443 \u043f\u043e\u0442\u043e\u043a\u043e\u0432.<\/p>\n<\/li>\n<li>\n<p>\u0428\u0430\u0433 \u21162 &#8212; \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u0435\u0442 \u0441\u043e\u0437\u0434\u0430\u043d\u043d\u044b\u0435 \u043f\u043e\u0434\u0437\u0430\u0434\u0430\u043d\u0438\u044f \u0438 \u0443\u0434\u0430\u043b\u044f\u0435\u0442 \u0438\u0445 \u043f\u043e\u0441\u043b\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f<\/p>\n<\/li>\n<\/ul>\n<p>\u0414\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u043f\u043e\u0434\u0441\u0438\u0441\u0442\u0435\u043c\u0443 \u043b\u043e\u0433\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0434\u043b\u044f \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u043d\u0438\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u043c\u0435\u0445\u0430\u043d\u0438\u0437\u043c\u0430 \u043c\u043d\u043e\u0433\u043e\u043f\u043e\u0442\u043e\u0447\u043d\u043e\u0439 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438.<\/p>\n<p><strong>\u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u043f\u043e\u0434\u0441\u0438\u0441\u0442\u0435\u043c\u044b \u043b\u043e\u0433\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f<\/strong><\/p>\n<p>\u0414\u043b\u044f \u043d\u0430\u0447\u0430\u043b\u0430 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0434\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u043b\u043e\u0433\u043e\u0432.<\/p>\n<pre><code class=\"sql\">SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [DBA].[ReindexJobLog]( [LogID] [int] IDENTITY(1,1) NOT NULL, [JobName] [nvarchar](128) NULL, [GroupNumber] [int] NULL, [Tablename] [sysname] NOT NULL, [IndexName] [nvarchar](128) NULL, [ActionType] [nvarchar](128) NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [DurationSeconds]  AS (datediff(second,[StartTime],[EndTime])), [DBName] [nvarchar](128) NULL, PRIMARY KEY CLUSTERED  ( [LogID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FIVE] ) ON [FIVE] GO  ALTER TABLE [DBA].[ReindexJobLog] ADD  DEFAULT (getdate()) FOR [StartTime] GO   <\/code><\/pre>\n<p>\u0414\u043b\u044f \u0431\u043e\u043b\u0435\u0435 \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0439 \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u043a\u043e\u0434\u0430 \u0438 \u0443\u043f\u0440\u043e\u0449\u0435\u043d\u0438\u044f \u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u044b \u0437\u0430\u0434\u0430\u043d\u0438\u044f (SQL Job), \u0441\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u0443\u044e \u0445\u0440\u0430\u043d\u0438\u043c\u0443\u044e \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0443 \u0434\u043b\u044f \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438.<\/p>\n<blockquote>\n<p>\ud83d\udea8\u041d\u0435 \u0437\u0430\u0431\u0443\u0434\u044c\u0442\u0435 \u0438\u0437\u043c\u0435\u043d\u0438\u0442\u044c \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u0435 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u0441\u043a\u0440\u0438\u043f\u0442\u0435<\/p>\n<\/blockquote>\n<pre><code class=\"sql\">SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO  Create PROCEDURE [DBA].[ReindexTableWithLogging] @JobName NVARCHAR(128),                                                 @GroupNumber INT,                         @DBName Nvarchar(128),                                                 @TableName SYSNAME,                                                 @SchemaName SYSNAME = 'dbo' AS BEGIN     SET NOCOUNT ON;     DECLARE @FullTableName NVARCHAR(256) = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);     DECLARE @LogID INT;     DECLARE @indexName NVARCHAR(256);     Declare @sql_updateStats NVARCHAR(max) DECLARE @Batch1_SQL NVARCHAR(MAX);     DECLARE @Batch2_SQL NVARCHAR(MAX);     DECLARE @Batch3_SQL NVARCHAR(MAX);     DECLARE IndexCursor CURSOR LOCAL FAST_FORWARD FOR         SELECT i.name AS IndexName         FROM sys.indexes i         WHERE i.name IS NOT NULL -- Exclude heaps           and object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))     OPEN IndexCursor;     FETCH NEXT FROM IndexCursor INTO @IndexName;     WHILE @@FETCH_STATUS = 0         begin             INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, Tablename, Indexname, ActionType, StartTime)             VALUES (@JobName, @GroupNumber, @DBName, @FullTableName, @indexName, 'ReBuild Index All indexes', GETDATE());             SET @LogID = SCOPE_IDENTITY();             PRINT 'Starting reindex for group ' + CAST(@GroupNumber AS NVARCHAR(10)) + '\\Table' + @FullTableName +                   ' Index ' + @indexname + ' at ' + CONVERT(NVARCHAR, GETDATE(), 120);             -- Run reindex             EXEC ('DBCC DBREINDEX(''' + @FullTableName + ''', ''' + @indexname + ''', 90);');             -- Update log             UPDATE [DatabaseName].DBA.ReindexJobLog             SET EndTime = GETDATE()             WHERE LogID = @LogID               and Tablename = @FullTableName               and indexname = @indexName;             PRINT 'Finished reindexing group ' + CAST(@GroupNumber AS NVARCHAR(10)) + '\\Table' + @FullTableName +                   ' at ' + CONVERT(NVARCHAR, GETDATE(), 120);             FETCH NEXT FROM IndexCursor INTO @IndexName;         END     INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, Tablename, Indexname, ActionType, StartTime)     VALUES (@JobName, @GroupNumber, @DBName,  @FullTableName, '', 'Update rest of statistics', GETDATE());     SET @LogID = SCOPE_IDENTITY();    if         (SELECT count(1)          FROM sys.stats s                   INNER JOIN sys.objects obj ON s.object_id = obj.object_id                   INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id          WHERE obj.type = 'U' -- Only user tables            AND sch.name = @SchemaName            AND obj.name = @TableName            AND NOT EXISTS (SELECT 1                            FROM sys.indexes i                            WHERE i.object_id = s.object_id                              AND i.index_id = s.stats_id)) &lt; 20         Begin              SELECT @sql_updateStats = STRING_AGG(                     'UPDATE STATISTICS ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name)                         + ' ' + QUOTENAME(s.name) + ' WITH FULLSCAN;',                     CHAR(13)                                       )             FROM sys.stats s                      INNER JOIN sys.objects obj ON s.object_id = obj.object_id                      INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id             WHERE obj.type = 'U' -- Only user tables               AND sch.name = @SchemaName               AND obj.name = @TableName               AND NOT EXISTS (SELECT 1                               FROM sys.indexes i                               WHERE i.object_id = s.object_id                                 AND i.index_id = s.stats_id);             IF @sql_updateStats IS NOT NULL                 EXEC sp_executesql @sql_updateStats;         end     else         Begin             IF OBJECT_ID('tempdb..#StatsWithBatch') IS NOT NULL                 DROP TABLE #StatsWithBatch;              CREATE TABLE #StatsWithBatch             (                 UpdateCommand NVARCHAR(MAX),                 BatchNumber   INT             )             INSERT INTO #StatsWithBatch (UpdateCommand, BatchNumber)             SELECT 'UPDATE STATISTICS ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name)                        + ' ' + QUOTENAME(s.name) + ' WITH FULLSCAN;' AS UpdateCommand,                    NTILE(3) OVER (ORDER BY s.stats_id) AS BatchNumber -- Divide work into 2 batches             FROM sys.stats s                      INNER JOIN sys.objects obj ON s.object_id = obj.object_id                      INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id             WHERE obj.type = 'U' -- Only user tables               AND sch.name = @SchemaName               AND obj.name = @TableName               AND NOT EXISTS (SELECT 1                               FROM sys.indexes i                               WHERE i.object_id = s.object_id                                 AND i.index_id = s.stats_id);             SELECT @Batch1_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 1;              IF (@Batch1_SQL IS NOT NULL AND LEN(@Batch1_SQL) &gt; 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 1 ---';                     PRINT @Batch1_SQL;                     EXEC sp_executesql @Batch1_SQL;                     PRINT '--- BATCH 1 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 1: No commands to execute. ---';                 END              SELECT @Batch2_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 2;              IF (@Batch2_SQL IS NOT NULL AND LEN(@Batch2_SQL) &gt; 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 2 ---';                     PRINT @Batch2_SQL;                     exec sp_executesql @Batch2_SQL;                     PRINT '--- BATCH 2 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 2: No commands to execute. ---';                 END             SELECT @Batch3_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 3;              IF (@Batch3_SQL IS NOT NULL AND LEN(@Batch3_SQL) &gt; 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 2 ---';                     PRINT @Batch3_SQL;                     exec sp_executesql @Batch3_SQL;                     PRINT '--- BATCH 3 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 3: No commands to execute. ---';                 END  DROP TABLE #StatsWithBatch;         END          UPDATE [DatabaseName].DBA.ReindexJobLog     SET EndTime = GETDATE()     WHERE LogID = @LogID       and Tablename = @FullTableName END <\/code><\/pre>\n<p>\u0412 \u044d\u0442\u043e\u0439 \u0445\u0440\u0430\u043d\u0438\u043c\u043e\u0439 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0435 \u043c\u044b \u043d\u0435 \u0442\u043e\u043b\u044c\u043a\u043e \u043f\u0435\u0440\u0435\u0441\u043e\u0437\u0434\u0430\u0435\u043c \u0438\u043d\u0434\u0435\u043a\u0441\u044b, \u043d\u043e \u0438 \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0435\u043c \u0432\u0441\u0435 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0442\u0430\u0431\u043b\u0438\u0446, \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u0441\u043e\u0437\u0434\u0430\u043d\u043d\u044b\u0435 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u043c \u0421\u0423\u0411\u0414.<\/p>\n<p><strong>\u0428\u0430\u0433 \u21161 &#8212; \u0421\u043e\u0437\u0434\u0430\u0435\u0442 \u0434\u0438\u043d\u0430\u043c\u0438\u0447\u0435\u0441\u043a\u0438\u0435 \u043f\u043e\u0434\u0437\u0430\u0434\u0430\u043d\u0438\u044f \u0432 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0435, \u0440\u0430\u0432\u043d\u043e\u043c \u0447\u0438\u0441\u043b\u0443 \u043f\u043e\u0442\u043e\u043a\u043e\u0432.<\/strong><\/p>\n<blockquote>\n<p>\ud83d\udea8\u041d\u0435 \u0437\u0430\u0431\u0443\u0434\u044c\u0442\u0435 \u0438\u0437\u043c\u0435\u043d\u0438\u0442\u044c \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u0435 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u0441\u043a\u0440\u0438\u043f\u0442\u0435<\/p>\n<\/blockquote>\n<pre><code class=\"sql\">Create PROCEDURE [DBA].[HotTablesReindexingJobCreating] @DBName Nvarchar(125), @TotalGroups int  AS BEGIN -- C\u043e\u0437\u0434\u0430\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0433\u043e\u0440\u044f\u0447\u0438\u0445 \u0442\u0430\u0431\u043b\u0438\u0446  IF OBJECT_ID('tempdb..#HotTables') IS NOT NULL DROP TABLE #HotTables; SELECT      ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNum,     QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS FullTableName,     t.name AS TableName,     SCHEMA_NAME(t.schema_id) AS SchemaName INTO #HotTables FROM sys.tables t CROSS APPLY fn_listextendedproperty('IsHotTable', 'SCHEMA', SCHEMA_NAME(t.schema_id), 'TABLE', t.name, NULL, NULL) ep WHERE ep.value = '1'   AND t.is_ms_shipped = 0;   IF NOT EXISTS (SELECT * FROM #HotTables) BEGIN     PRINT 'No hot tables found.';     RETURN; END  -- \u041e\u043f\u0440\u0435\u0434\u0435\u043b\u0438\u0442\u044c \u0441\u043a\u043e\u043b\u044c\u043a\u043e \u043f\u043e\u0442\u043e\u043a\u043e\u0432 DECLARE @i INT = 1;  WHILE @i &lt;= @TotalGroups BEGIN     DECLARE @JobName NVARCHAR(128) = @DBName + N'_Reindex_HotTables_Group_' + CAST(@i AS NVARCHAR(5));     DECLARE @SqlCommand NVARCHAR(MAX);     -- \u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u0434\u0438\u043d\u0430\u043c\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0437\u0430\u0434\u0430\u043d\u0438\u0439     SET @SqlCommand = '';     SELECT @SqlCommand = @SqlCommand +          'EXEC [DatabaseName].dba.ReindexTableWithLogging @JobName = ''' + @JobName + ''', @GroupNumber = ' + CAST(@i AS NVARCHAR(10)) +', @DBName = ''' + @DBName + ''' , @TableName = ''' + t.TableName + ''', @SchemaName = ''' +t.SchemaName + ''';' + CHAR(13)+CHAR(10)     FROM #HotTables t     WHERE t.RowNum % @TotalGroups = @i - 1;           IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)         EXEC msdb.dbo.sp_delete_job @job_name = @JobName;           EXEC msdb.dbo.sp_add_job         @job_name = @JobName,         @enabled = 1,         @description = 'Auto-generated reindex job using stored procedure',         @category_name = 'Database Maintenance';           EXEC msdb.dbo.sp_add_jobstep         @job_name = @JobName,         @step_name = N'Reindex Tables',         @subsystem = N'TSQL', @database_name = @DBName,         @command = @SqlCommand,         @retry_attempts = 0,         @retry_interval = 0;         EXEC msdb.dbo.sp_add_jobschedule         @job_name = @JobName,         @name = 'RunOnceNow',         @freq_type = 1,         @active_start_time = 0;     EXEC msdb.dbo.sp_add_jobserver         @job_name = @JobName,         @server_name = N'(local)';     -- \u0437\u0430\u043f\u0443\u0441\u043a     EXEC msdb.dbo.sp_start_job @job_name = @JobName;     SET @i = @i + 1; END END <\/code><\/pre>\n<p><strong>\u0428\u0430\u0433 \u21162 &#8212; \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u0435\u0442 \u0441\u043e\u0437\u0434\u0430\u043d\u043d\u044b\u0435 \u043f\u043e\u0434\u0437\u0430\u0434\u0430\u043d\u0438\u044f \u0438 \u0443\u0434\u0430\u043b\u044f\u0435\u0442 \u0438\u0445 \u043f\u043e\u0441\u043b\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f<\/strong><\/p>\n<pre><code class=\"sql\">Create proc [DBA].[CleaningReindexingJob] @DBName Nvarchar(128) as begin  DECLARE @JobName NVARCHAR(128); DECLARE @i INT; DECLARE @IsRunning BIT; DECLARE @WaitInterval CHAR(8) = '00:30:00'; -- 30-minute wait interval   WAITFOR DELAY @WaitInterval; ---------------------------------------------------------------------- -- Section 1: Process the 'Hot' table jobs (1 to 3) ---------------------------------------------------------------------- PRINT '--- Checking HOT Table Jobs ---'; SET @i = 1;  WHILE @i &lt;= 3 BEGIN \u00a0 \u00a0 SET @JobName = @DBName + '_Reindex_HotTables_Group_' + CAST(@i AS NVARCHAR(5));  \u00a0 \u00a0 IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @JobName) \u00a0 \u00a0 BEGIN \u00a0 \u00a0 \u00a0 \u00a0 PRINT 'Checking job: ' + @JobName; \u00a0 \u00a0 \u00a0 \u00a0 SET @IsRunning = 1;  \u00a0 \u00a0 \u00a0 \u00a0 WHILE @IsRunning = 1 \u00a0 \u00a0 \u00a0 \u00a0 BEGIN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT @IsRunning = COUNT(*) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM msdb.dbo.sysjobactivity ja \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 WHERE j.name = @JobName AND ja.start_execution_date IS NOT NULL AND ja.stop_execution_date IS NULL;  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IF @IsRunning = 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 BEGIN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 PRINT 'Job ' + @JobName + ' is still running. Waiting for 30 minutes...'; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 WAITFOR DELAY @WaitInterval; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 END \u00a0 \u00a0 \u00a0 \u00a0 END  \u00a0 \u00a0 \u00a0 \u00a0 EXEC msdb.dbo.sp_delete_job @job_name = @JobName; \u00a0 \u00a0 \u00a0 \u00a0 PRINT '\u2705 Job deleted: ' + @JobName; \u00a0 \u00a0 END \u00a0 \u00a0 ELSE \u00a0 \u00a0 BEGIN \u00a0 \u00a0 \u00a0 \u00a0 PRINT '\u274c Job does not exist: ' + @JobName; \u00a0 \u00a0 END  \u00a0 \u00a0 SET @i = @i + 1; END PRINT '--- Finished checking HOT Table Jobs ---'; PRINT ''; -- Add a blank line for readability  ---------------------------------------------------------------------- -- Section 2: Process the 'Warm' table jobs (1 to 10) ---------------------------------------------------------------------- PRINT '--- Checking WARM Table Jobs ---'; SET @i = 1; -- Important: Reset the counter to 1  WHILE @i &lt;= 10 BEGIN \u00a0 \u00a0 SET @JobName = @DBName + '_Reindex_WarmTables_Group_' + CAST(@i AS NVARCHAR(5));  \u00a0 \u00a0 IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @JobName) \u00a0 \u00a0 BEGIN \u00a0 \u00a0 \u00a0 \u00a0 PRINT 'Checking job: ' + @JobName; \u00a0 \u00a0 \u00a0 \u00a0 SET @IsRunning = 1;  \u00a0 \u00a0 \u00a0 \u00a0 WHILE @IsRunning = 1 \u00a0 \u00a0 \u00a0 \u00a0 BEGIN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT @IsRunning = COUNT(*) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 FROM msdb.dbo.sysjobactivity ja \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 WHERE j.name = @JobName AND ja.start_execution_date IS NOT NULL AND ja.stop_execution_date IS NULL;  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IF @IsRunning = 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 BEGIN \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 PRINT 'Job ' + @JobName + ' is still running. Waiting for 30 minutes...'; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 WAITFOR DELAY @WaitInterval; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 END \u00a0 \u00a0 \u00a0 \u00a0 END  \u00a0 \u00a0 \u00a0 \u00a0 EXEC msdb.dbo.sp_delete_job @job_name = @JobName; \u00a0 \u00a0 \u00a0 \u00a0 PRINT '\u2705 Job deleted: ' + @JobName; \u00a0 \u00a0 END \u00a0 \u00a0 ELSE \u00a0 \u00a0 BEGIN \u00a0 \u00a0 \u00a0 \u00a0 PRINT '\u274c Job does not exist: ' + @JobName; \u00a0 \u00a0 END  \u00a0 \u00a0 SET @i = @i + 1; END PRINT '--- Finished checking WARM Table Jobs ---'; ENd GO <\/code><\/pre>\n<h2>\u041e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435 \u0442\u0435\u043f\u043b\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446<\/h2>\n<p>\u0414\u043b\u044f \u0442\u0435\u043f\u043b\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u043f\u0440\u0435\u0434\u0443\u0441\u043c\u043e\u0442\u0440\u0435\u043d\u0430 \u0435\u0436\u0435\u0434\u043d\u0435\u0432\u043d\u0430\u044f \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f \u043d\u0430 \u043e\u0441\u043d\u043e\u0432\u0435 \u0443\u0440\u043e\u0432\u043d\u044f \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438:<\/p>\n<div>\n<div class=\"table\">\n<table>\n<tbody>\n<tr>\n<th>\n<p align=\"left\"><strong>\u0423\u0440\u043e\u0432\u0435\u043d\u044c \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438<\/strong><\/p>\n<\/th>\n<th>\n<p align=\"left\"><strong>\u0414\u0435\u0439\u0441\u0442\u0432\u0438\u0435<\/strong><\/p>\n<\/th>\n<\/tr>\n<tr>\n<td>\n<p align=\"left\">&lt; 5%<\/p>\n<\/td>\n<td>\n<p align=\"left\">\u041e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"left\">5% &#8212; 30%<\/p>\n<\/td>\n<td>\n<p align=\"left\">\u0420\u0435\u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u044f (Reorganize) \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"left\">\u2265 30%<\/p>\n<\/td>\n<td>\n<p align=\"left\">\u041f\u0435\u0440\u0435\u0441\u0442\u0440\u043e\u0435\u043d\u0438\u0435 (Rebuild) \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>\u0414\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u043f\u043b\u0430\u043d\u0438\u0440\u0443\u0435\u0442\u0441\u044f \u0435\u0436\u0435\u043d\u0435\u0434\u0435\u043b\u044c\u043d\u043e\u0435 \u043f\u0435\u0440\u0435\u0441\u0442\u0440\u043e\u0435\u043d\u0438\u0435 (Rebuild) \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0432\u043d\u0435 \u0437\u0430\u0432\u0438\u0441\u0438\u043c\u043e\u0441\u0442\u0438 \u043e\u0442 \u0443\u0440\u043e\u0432\u043d\u044f \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438.<\/p>\n<p>\u0414\u043b\u044f \u0431\u043e\u043b\u0435\u0435 \u0447\u0438\u0441\u0442\u043e\u0439 \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u043a\u043e\u0434\u0430 \u0438 \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0430\u043d\u0438\u044f \u0432\u044b\u0441\u043e\u043a\u043e\u0433\u043e \u0443\u0440\u043e\u0432\u043d\u044f \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438 \u043f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u0435\u0442\u0441\u044f \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u0443\u044e \u0445\u0440\u0430\u043d\u0438\u043c\u0443\u044e \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0443 \u0434\u043b\u044f \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0438 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0442\u0435\u043f\u043b\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446.<\/p>\n<blockquote>\n<p>\ud83d\udea8\u041d\u0435 \u0437\u0430\u0431\u0443\u0434\u044c\u0442\u0435 \u0438\u0437\u043c\u0435\u043d\u0438\u0442\u044c \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u0435 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u0441\u043a\u0440\u0438\u043f\u0442\u0435<\/p>\n<\/blockquote>\n<pre><code class=\"sql\">SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE PROCEDURE [DBA].[ReindexTableWithStats] @DBName Nvarchar(128),                                               @SchemaName SYSNAME,                                               @TableName SYSNAME,                                               @JobName NVARCHAR(128),                                               @GroupNumber INT AS BEGIN     SET NOCOUNT ON;     DECLARE @IndexName SYSNAME;     DECLARE @IndexType NVARCHAR(150);     DECLARE @Fragmentation FLOAT;     DECLARE @SQL NVARCHAR(MAX);     DECLARE @Action NVARCHAR(50);     Declare @sql_updateStats NVARCHAR(MAX);     DECLARE @Batch1_SQL NVARCHAR(MAX);     DECLARE @Batch2_SQL NVARCHAR(MAX);     DECLARE @Batch3_SQL NVARCHAR(MAX);     -- Cursor to loop through indexes of the table     DECLARE IndexCursor CURSOR LOCAL FAST_FORWARD FOR         SELECT i.name AS IndexName,                ips.index_type_desc,                ips.avg_fragmentation_in_percent         FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)),                                             NULL, NULL, 'LIMITED') ips                  JOIN              sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id         WHERE i.name IS NOT NULL -- Exclude heaps           and ips.alloc_unit_type_desc = 'IN_ROW_DATA';      OPEN IndexCursor;     FETCH NEXT FROM IndexCursor INTO @IndexName, @IndexType, @Fragmentation;      WHILE @@FETCH_STATUS = 0         BEGIN             -- Determine action based on fragmentation             IF @Fragmentation &lt; 5                 BEGIN                     SET @Action = 'Update Statistics Only';                     SET @SQL = 'UPDATE STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ' +                                QUOTENAME(@IndexName) + ' with FullScan;';                 END             ELSE                 IF @Fragmentation BETWEEN 5 AND 30                     BEGIN                         SET @Action = 'Reorganize + Update Statistics';                         SET @SQL = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' +                                    QUOTENAME(@TableName) + ' REORGANIZE;                         UPDATE STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ' +                                    QUOTENAME(@IndexName) + ' with FullScan;';                     END                 ELSE                     BEGIN                         SET @Action = 'Rebuild';                         -- Use DBCC DBREINDEX instead of ALTER INDEX                         SET @SQL = 'DBCC DBREINDEX(''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ''', ' +                                    QUOTENAME(@IndexName) + ', 90);';                     END              -- Log start of action             INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, TableName, IndexName, ActionType,                                                    StartTime)             VALUES (@JobName, @GroupNumber, @DBName, QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName), @IndexName,                     @Action, GETDATE());              DECLARE @LogID INT = SCOPE_IDENTITY();              PRINT 'Starting: ' + @Action + ' on ' + @SchemaName + '.' + @TableName + '.' + @IndexName;              BEGIN TRY                 EXEC sp_executesql @SQL;                 PRINT 'Finished: ' + @Action + ' on ' + @SchemaName + '.' + @TableName + '.' + @IndexName;                  -- Log end time                 UPDATE [DatabaseName].dba.ReindexJobLog                 SET EndTime = GETDATE()                 WHERE LogID = @LogID;             END TRY             BEGIN CATCH                 PRINT 'Error occurred during: ' + @Action + ' on ' + @SchemaName + '.' + @TableName + '.' + @IndexName;                 PRINT ERROR_MESSAGE();                  UPDATE [DatabaseName].dba.ReindexJobLog                 SET EndTime = GETDATE()                 WHERE LogID = @LogID;             END CATCH              FETCH NEXT FROM IndexCursor INTO @IndexName, @IndexType, @Fragmentation;         END     CLOSE IndexCursor;     DEALLOCATE IndexCursor;      --Update the rest of statistics     INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, Tablename, Indexname, ActionType, StartTime)     VALUES (@JobName, @GroupNumber, @DBName, QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName), '',             'Update rest of statistics', GETDATE());     SET @LogID = SCOPE_IDENTITY();     if         (SELECT count(1)          FROM sys.stats s                   INNER JOIN sys.objects obj ON s.object_id = obj.object_id                   INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id          WHERE obj.type = 'U' -- Only user tables            AND sch.name = @SchemaName            AND obj.name = @TableName            AND NOT EXISTS (SELECT 1                            FROM sys.indexes i                            WHERE i.object_id = s.object_id                              AND i.index_id = s.stats_id)) &lt; 20         Begin              SELECT @sql_updateStats = STRING_AGG(                     'UPDATE STATISTICS ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name)                         + ' ' + QUOTENAME(s.name) + ' WITH FULLSCAN;',                     CHAR(13)                                       )             FROM sys.stats s                      INNER JOIN sys.objects obj ON s.object_id = obj.object_id                      INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id             WHERE obj.type = 'U' -- Only user tables               AND sch.name = @SchemaName               AND obj.name = @TableName               AND NOT EXISTS (SELECT 1                               FROM sys.indexes i                               WHERE i.object_id = s.object_id                                 AND i.index_id = s.stats_id);             IF @sql_updateStats IS NOT NULL                 EXEC sp_executesql @sql_updateStats;         end     else         Begin             IF OBJECT_ID('tempdb..#StatsWithBatch') IS NOT NULL                 DROP TABLE #StatsWithBatch;              CREATE TABLE #StatsWithBatch             (                 UpdateCommand NVARCHAR(MAX),                 BatchNumber   INT             )             INSERT INTO #StatsWithBatch (UpdateCommand, BatchNumber)             SELECT 'UPDATE STATISTICS ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name)                        + ' ' + QUOTENAME(s.name) + ' WITH FULLSCAN;' AS UpdateCommand,                    NTILE(3) OVER (ORDER BY s.stats_id) AS BatchNumber -- Divide work into 2 batches             FROM sys.stats s                      INNER JOIN sys.objects obj ON s.object_id = obj.object_id                      INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id             WHERE obj.type = 'U' -- Only user tables               AND sch.name = @SchemaName               AND obj.name = @TableName               AND NOT EXISTS (SELECT 1                               FROM sys.indexes i                               WHERE i.object_id = s.object_id                                 AND i.index_id = s.stats_id);             SELECT @Batch1_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 1;              IF (@Batch1_SQL IS NOT NULL AND LEN(@Batch1_SQL) &gt; 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 1 ---';                     PRINT @Batch1_SQL;                     EXEC sp_executesql @Batch1_SQL;                     PRINT '--- BATCH 1 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 1: No commands to execute. ---';                 END              SELECT @Batch2_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 2;              IF (@Batch2_SQL IS NOT NULL AND LEN(@Batch2_SQL) &gt; 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 2 ---';                     PRINT @Batch2_SQL;                     exec sp_executesql @Batch2_SQL;                     PRINT '--- BATCH 2 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 2: No commands to execute. ---';                 END             SELECT @Batch3_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 3;              IF (@Batch3_SQL IS NOT NULL AND LEN(@Batch3_SQL) &gt; 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 2 ---';                     PRINT @Batch3_SQL;                     exec sp_executesql @Batch3_SQL;                     PRINT '--- BATCH 3 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 3: No commands to execute. ---';                 END DROP TABLE #StatsWithBatch;         END           UPDATE [DatabaseName].DBA.ReindexJobLog     SET EndTime = GETDATE()     WHERE LogID = @LogID       and Tablename = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) END GO<\/code><\/pre>\n<p>\u0410\u043d\u0430\u043b\u043e\u0433\u0438\u0447\u043d\u044b\u043c \u043e\u0431\u0440\u0430\u0437\u043e\u043c \u0431\u0443\u0434\u0435\u0442 \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u043e\u0432\u0430\u043d\u043e \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435 \u0442\u0435\u043f\u043b\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446. \u0418\u0445 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f \u0431\u0443\u0434\u0435\u0442 \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0442\u044c\u0441\u044f \u0432 \u043c\u043d\u043e\u0433\u043e\u043f\u043e\u0442\u043e\u0447\u043d\u043e\u043c \u0440\u0435\u0436\u0438\u043c\u0435 \u0434\u043b\u044f \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438.<\/p>\n<p>\u0425\u0440\u0430\u043d\u0438\u043c\u0430\u044f \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0430 \u0434\u043b\u044f \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u0437\u0430\u0434\u0430\u043d\u0438\u044f \u043f\u043e \u0437\u0430\u043f\u0443\u0441\u043a\u0443 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0442\u0435\u043f\u043b\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446<\/p>\n<pre><code class=\"sql\">Create PROCEDURE [DBA].[WarmTablesReindexingJobCreating] @DBName Nvarchar(125), @TotalGroups int AS BEGIN -- C\u043e\u0437\u0434\u0430\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0442\u0430\u0431\u043b\u0438\u0446  IF OBJECT_ID('tempdb..#warmTables') IS NOT NULL DROP TABLE #warmTables; SELECT      ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNum,     QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS FullTableName,     t.name AS TableName,     SCHEMA_NAME(t.schema_id) AS SchemaName INTO #warmTables FROM sys.tables t inner join    sys.schemas s ON t.schema_id = s.schema_id WHERE      NOT EXISTS (         SELECT 1         FROM fn_listextendedproperty('IsHotTable', 'SCHEMA', s.name, 'TABLE', t.name, NULL, NULL) ep         WHERE ep.value = '1'     ) and t.name not like 'X_%'   IF NOT EXISTS (SELECT * FROM #warmTables) BEGIN     PRINT 'No hot tables found.';     RETURN; END  -- \u041e\u043f\u0440\u0435\u0434\u0435\u043b\u0438\u0442\u044c \u0441\u043a\u043e\u043b\u044c\u043a\u043e \u043f\u043e\u0442\u043e\u043a\u043e\u0432  DECLARE @i INT = 1;   WHILE @i &lt;= @TotalGroups BEGIN     DECLARE @JobName NVARCHAR(128) = @DBName +N'_Reindex_WarmTables_Group_' + CAST(@i AS NVARCHAR(5));     DECLARE @SqlCommand NVARCHAR(MAX);      -- \u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u0434\u0438\u043d\u0430\u043c\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0437\u0430\u0434\u0430\u043d\u0438\u0439     SET @SqlCommand = '';       SELECT @SqlCommand = @SqlCommand +          'EXEC [DatabaseName].dba.ReindexTableWithStats @JobName = ''' + @JobName + ''', @GroupNumber = ' + CAST(@i AS NVARCHAR(10)) + ', @DBName = ''' + @DBName + '''' + ', @TableName = ''' + t.TableName + ''', @SchemaName = ''' +t.SchemaName + ''';' + CHAR(13)+CHAR(10)     FROM #warmTables t     WHERE t.RowNum % @TotalGroups = @i - 1;           IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)         EXEC msdb.dbo.sp_delete_job @job_name = @JobName;           EXEC msdb.dbo.sp_add_job         @job_name = @JobName,         @enabled = 1,         @description = 'Auto-generated reindex job using stored procedure',         @category_name = 'Database Maintenance';           EXEC msdb.dbo.sp_add_jobstep         @job_name = @JobName,         @step_name = N'Reindex Tables',         @subsystem = N'TSQL', @database_name = @DBName,         @command = @SqlCommand,         @retry_attempts = 0,         @retry_interval = 0;           EXEC msdb.dbo.sp_add_jobschedule         @job_name = @JobName,         @name = 'RunOnceNow',         @freq_type = 1,         @active_start_time = 0;           EXEC msdb.dbo.sp_add_jobserver         @job_name = @JobName,         @server_name = N'(local)';      -- \u0437\u0430\u043f\u0443\u0441\u043a    EXEC msdb.dbo.sp_start_job @job_name = @JobName;      SET @i = @i + 1; END END <\/code><\/pre>\n<blockquote>\n<p>\ud83d\udea7 \u0411\u044b\u043b\u043e \u0431\u044b \u0445\u043e\u0440\u043e\u0448\u043e, \u0435\u0441\u043b\u0438 \u0431\u044b \u0438\u043c\u0435\u043b\u0430\u0441\u044c \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044c \u0435\u0436\u0435\u043d\u0435\u0434\u0435\u043b\u044c\u043d\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0432\u0441\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0425\u041f ReindexTableWithLogging<\/p>\n<\/blockquote>\n<p>\u0414\u043b\u044f \u0443\u043f\u0440\u043e\u0449\u0435\u043d\u0438\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0430\u0434\u043c\u0438\u043d\u0438\u0441\u0442\u0440\u0430\u0442\u043e\u0440\u043e\u0432 \u0421\u0423\u0411\u0414, \u044f \u043e\u0431\u044a\u0435\u0434\u0438\u043d\u0438\u043b \u0432\u0441\u044e \u0441\u0438\u0441\u0442\u0435\u043c\u0443 \u0432 \u0435\u0434\u0438\u043d\u044b\u0439 \u0441\u043a\u0440\u0438\u043f\u0442, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u0441\u043e\u0437\u0434\u0430\u0435\u0442 \u0437\u0430\u0434\u0430\u043d\u0438\u0435 \u0432 SQL Agent Job<\/p>\n<pre><code class=\"sql\">USE [msdb] GO  \/****** Object:  Job [Smart_Reindex_]    Script Date: 20.08.2025 17:23:15 ******\/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 \/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 20.08.2025 17:23:16 ******\/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback  END  DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Smart_Reindex_',  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=0,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N'No description available.',  @category_name=N'[Uncategorized (Local)]',  @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback \/****** Object:  Step [HotTable reindex]    Script Date: 20.08.2025 17:23:16 ******\/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'HotTable reindex',  @step_id=1,  @cmdexec_success_code=0,  @on_success_action=3,  @on_success_step_id=0,  @on_fail_action=2,  @on_fail_step_id=0,  @retry_attempts=0,  @retry_interval=0,  @os_run_priority=0, @subsystem=N'TSQL',  @command=N'Exec [DatabaseName].DBA.HotTablesReindexingJobCreating @DBName = ''[DatabaseName]'', @TotalGroups = 5',  @database_name=N'[DatabaseName]',  @flags=0 IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback \/****** Object:  Step [WarmTables]    Script Date: 20.08.2025 17:23:16 ******\/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'WarmTables',  @step_id=2,  @cmdexec_success_code=0,  @on_success_action=3,  @on_success_step_id=0,  @on_fail_action=3,  @on_fail_step_id=0,  @retry_attempts=0,  @retry_interval=0,  @os_run_priority=0, @subsystem=N'TSQL',  @command=N'Exec [DatabaseName].DBA.WarmTablesReindexingJobCreating @DBName = ''[DatabaseName]'', @TotalGroups = 15',  @database_name=N'[DatabaseName]',  @flags=0 IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback \/****** Object:  Step [Clear_after_reindexing]    Script Date: 20.08.2025 17:23:16 ******\/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Clear_after_reindexing',  @step_id=3,  @cmdexec_success_code=0,  @on_success_action=1,  @on_success_step_id=0,  @on_fail_action=2,  @on_fail_step_id=0,  @retry_attempts=0,  @retry_interval=0,  @os_run_priority=0, @subsystem=N'TSQL',  @command=N'Exec [DatabaseName].DBA.CleaningReindexingJob @DBName = ''[DatabaseName]''',  @database_name=N'[DatabaseName]',  @flags=0 IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly_mon_Fri_18-00',  @enabled=1,  @freq_type=8,  @freq_interval=63,  @freq_subday_type=1,  @freq_subday_interval=0,  @freq_relative_interval=0,  @freq_recurrence_factor=1,  @active_start_date=20250718,  @active_end_date=99991231,  @active_start_time=201000,  @active_end_time=235959,  @schedule_uid=N'b8daac26-30d1-4343-b6a8-3814669bdf83' IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback:     IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION EndSave: GO   <\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/939786\/\"> https:\/\/habr.com\/ru\/articles\/939786\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p>\u0414\u0430\u043d\u043d\u044b\u0439 \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u0441\u043e\u0431\u043e\u0439 \u0441\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044e \u043f\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0442\u0430\u0431\u043b\u0438\u0446 \u0432 \u043a\u0440\u0443\u043f\u043d\u044b\u0445 \u0431\u0430\u0437\u0430\u0445 \u0434\u0430\u043d\u043d\u044b\u0445 (VLDB), \u043d\u0430\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u043d\u0443\u044e \u043d\u0430 \u043e\u0431\u0435\u0441\u043f\u0435\u0447\u0435\u043d\u0438\u0435 \u0438\u0445 \u0441\u0442\u0430\u0431\u0438\u043b\u044c\u043d\u043e\u0439 \u0440\u0430\u0431\u043e\u0442\u044b, \u0432\u044b\u0441\u043e\u043a\u043e\u0439 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0438 \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0432.<\/p>\n<p>\u0414\u0430\u043d\u043d\u0430\u044f \u0441\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044f \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0430\u043d\u0430 \u0441 \u0443\u0447\u0435\u0442\u043e\u043c \u0441\u043f\u0435\u0446\u0438\u0444\u0438\u043a\u0438 \u0440\u0430\u0431\u043e\u0442\u044b \u0441 \u0431\u043e\u043b\u044c\u0448\u0438\u043c\u0438 \u043e\u0431\u044a\u0435\u043c\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u043e\u0440\u0438\u0435\u043d\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0430 \u043d\u0430 \u043c\u0438\u043d\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044e \u043f\u0440\u043e\u0441\u0442\u043e\u0435\u0432 \u0441\u0438\u0441\u0442\u0435\u043c\u044b \u043f\u0440\u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0438 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u044e\u0449\u0438\u0445 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439.<\/p>\n<h2>\u041f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f<\/h2>\n<p>\u041e\u0441\u043d\u043e\u0432\u043d\u0430\u044f \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0430 \u043f\u0440\u0438 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0438 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 (VLDB) \u0437\u0430\u043a\u043b\u044e\u0447\u0430\u0435\u0442\u0441\u044f \u0432 \u0442\u043e\u043c, \u0447\u0442\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f \u0432\u0441\u0435\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u043c\u043e\u0436\u0435\u0442 \u0437\u0430\u043d\u0438\u043c\u0430\u0442\u044c \u043e\u0447\u0435\u043d\u044c \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0435 \u0432\u0440\u0435\u043c\u044f, \u0438\u043d\u043e\u0433\u0434\u0430 \u0440\u0430\u0441\u0442\u044f\u0433\u0438\u0432\u0430\u044f\u0441\u044c \u043d\u0430 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0434\u043d\u0435\u0439. \u042d\u0442\u043e \u0441\u043e\u0437\u0434\u0430\u0435\u0442 \u0441\u0435\u0440\u044c\u0435\u0437\u043d\u044b\u0435 \u0442\u0440\u0443\u0434\u043d\u043e\u0441\u0442\u0438 \u0434\u043b\u044f \u043e\u0431\u0435\u0441\u043f\u0435\u0447\u0435\u043d\u0438\u044f \u043d\u0435\u043f\u0440\u0435\u0440\u044b\u0432\u043d\u043e\u0441\u0442\u0438 \u0431\u0438\u0437\u043d\u0435\u0441-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u043e\u0432 \u0438 \u0442\u0440\u0435\u0431\u0443\u0435\u0442 \u0441\u043f\u0435\u0446\u0438\u0430\u043b\u044c\u043d\u044b\u0445 \u043f\u043e\u0434\u0445\u043e\u0434\u043e\u0432 \u043a \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0434\u0430\u043d\u043d\u043e\u0433\u043e \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430.<\/p>\n<h3>\u041f\u0440\u043e\u0431\u043b\u0435\u043c\u044b \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u043e\u0439 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438<\/h3>\n<p>\u041f\u0440\u0438 \u0440\u0430\u0431\u043e\u0442\u0435 \u0441 \u043a\u0440\u0443\u043f\u043d\u044b\u043c\u0438 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u044b\u0435 \u043f\u043e\u0434\u0445\u043e\u0434\u044b \u043a \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0441\u0442\u0430\u043b\u043a\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0441\u043e \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u043c\u0438 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0430\u043c\u0438:<\/p>\n<ul>\n<li>\n<p><strong>\u0414\u043b\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0435 \u0432\u0440\u0435\u043c\u044f \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f:<\/strong> \u043f\u043e\u043b\u043d\u0430\u044f \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u044f \u0432\u0441\u0435\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u043c\u043e\u0436\u0435\u0442 \u0437\u0430\u043d\u0438\u043c\u0430\u0442\u044c \u0434\u043d\u0438, \u0447\u0442\u043e \u043d\u0435\u043f\u0440\u0438\u0435\u043c\u043b\u0435\u043c\u043e \u0434\u043b\u044f \u0441\u0438\u0441\u0442\u0435\u043c \u0441 \u0442\u0440\u0435\u0431\u043e\u0432\u0430\u043d\u0438\u044f\u043c\u0438 \u0432\u044b\u0441\u043e\u043a\u043e\u0439 \u0434\u043e\u0441\u0442\u0443\u043f\u043d\u043e\u0441\u0442\u0438.<\/p>\n<\/li>\n<li>\n<p><strong>\u0412\u044b\u0441\u043e\u043a\u0430\u044f \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0430 \u043d\u0430 \u0441\u0438\u0441\u0442\u0435\u043c\u0443:<\/strong> \u043f\u0440\u043e\u0446\u0435\u0441\u0441 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u044f\u0435\u0442 \u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0440\u0435\u0441\u0443\u0440\u0441\u044b \u0441\u0435\u0440\u0432\u0435\u0440\u0430, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u043d\u0435\u0433\u0430\u0442\u0438\u0432\u043d\u043e \u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0442\u044c\u0441\u044f \u043d\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043e\u0441\u043d\u043e\u0432\u043d\u044b\u0445 \u0440\u0430\u0431\u043e\u0447\u0438\u0445 \u043d\u0430\u0433\u0440\u0443\u0437\u043e\u043a.<\/p>\n<\/li>\n<li>\n<p><strong>\u0411\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043a\u0438:<\/strong> \u043c\u043d\u043e\u0433\u0438\u0435 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0438 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0442\u0440\u0435\u0431\u0443\u044e\u0442 \u0443\u0441\u0442\u0430\u043d\u043e\u0432\u043a\u0438 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043e\u043a, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442\u044c \u043a \u043f\u0440\u043e\u0441\u0442\u043e\u044f\u043c \u0432 \u0440\u0430\u0431\u043e\u0442\u0435 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439.<\/p>\n<\/li>\n<\/ul>\n<h3>\u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438<\/h3>\n<p>\u0414\u043b\u044f \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u0443\u043a\u0430\u0437\u0430\u043d\u043d\u044b\u0445 \u043f\u0440\u043e\u0431\u043b\u0435\u043c \u043f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u0435\u0442\u0441\u044f \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434:<\/p>\n<p>\u0412\u043c\u0435\u0441\u0442\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0432\u0441\u0435\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0440\u0430\u0437\u0434\u0435\u043b\u0438\u0442\u044c \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u043d\u0430 2 \u0433\u0440\u0443\u043f\u043f\u044b:<\/p>\n<ul>\n<li>\n<p><strong>\u0413\u043e\u0440\u044f\u0447\u0438\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b<\/strong>: <\/p>\n<ul>\n<li>\n<p>\u0412\u044b\u0441\u043e\u043a\u0430\u044f \u0447\u0430\u0441\u0442\u043e\u0442\u0430 \u043e\u0431\u0440\u0430\u0449\u0435\u043d\u0438\u0439 (\u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432) \u043a \u0442\u0430\u0431\u043b\u0438\u0446\u0435.<\/p>\n<\/li>\n<li>\n<p>\u0427\u0430\u0441\u0442\u044b\u0435 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u0434\u0430\u043d\u043d\u044b\u0445 (INSERT \/ UPDATE \/ DELETE).<\/p>\n<\/li>\n<li>\n<p>\u0423\u0447\u0430\u0441\u0442\u0438\u0435 \u0432 \u043d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u0432\u0430\u0436\u043d\u044b\u0445 \u0431\u0438\u0437\u043d\u0435\u0441-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430\u0445 \u0438\u043b\u0438 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044f\u0445.<\/p>\n<\/li>\n<li>\n<p>\u041a\u0440\u0438\u0442\u0438\u0447\u043d\u043e\u0441\u0442\u044c \u0434\u043b\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0441\u0438\u0441\u0442\u0435\u043c\u044b.<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<li>\n<p><strong>\u0422\u0435\u043f\u043b\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b:<\/strong> <\/p>\n<ul>\n<li>\n<p>\u0418\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044e\u0442\u0441\u044f \u0443\u043c\u0435\u0440\u0435\u043d\u043d\u043e.<\/p>\n<\/li>\n<li>\n<p>\u041c\u043e\u0433\u0443\u0442 \u0431\u044b\u0442\u044c \u0447\u0430\u0441\u0442\u044c\u044e \u043f\u0440\u043e\u043c\u0435\u0436\u0443\u0442\u043e\u0447\u043d\u044b\u0445 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u043e\u0432 \u0438\u043b\u0438 \u043e\u0442\u0447\u0435\u0442\u043d\u043e\u0441\u0442\u0438.<\/p>\n<\/li>\n<li>\n<p>\u041d\u0435 \u0442\u0430\u043a \u043a\u0440\u0438\u0442\u0438\u0447\u043d\u044b \u0434\u043b\u044f \u0435\u0436\u0435\u0434\u043d\u0435\u0432\u043d\u043e\u0439 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u043e\u043d\u043d\u043e\u0439 \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0438.<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h4>\u041e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u0435 \u043a\u0430\u0442\u0435\u0433\u043e\u0440\u0438\u0439 \u0442\u0430\u0431\u043b\u0438\u0446<\/h4>\n<p>\u0421\u0443\u0449\u0435\u0441\u0442\u0432\u0443\u0435\u0442 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u043c\u0435\u0442\u043e\u0434\u043e\u0432 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u044f \u0447\u0430\u0441\u0442\u043e\u0442\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446:<\/p>\n<ul>\n<li>\n<p><strong>\u0410\u043d\u0430\u043b\u0438\u0437 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432<\/strong><\/p>\n<\/li>\n<li>\n<p><strong>\u0418\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 Extended Events<\/strong><\/p>\n<\/li>\n<li>\n<p><strong>\u0410\u043d\u0430\u043b\u0438\u0437 \u043a\u044d\u0448\u0430 \u043f\u043b\u0430\u043d\u043e\u0432 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432<\/strong><\/p>\n<\/li>\n<li>\n<p><strong>\u041f\u0440\u0438\u043c\u0435\u043d\u0435\u043d\u0438\u0435 SQL Default Trace<\/strong><\/p>\n<\/li>\n<li>\n<p><strong>\u0410\u043d\u0430\u043b\u0438\u0437 \u0442\u0440\u0435\u0439\u0441\u043e\u0432 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e SQL Profiler<\/strong><\/p>\n<\/li>\n<\/ul>\n<p>\u0412 \u043d\u0430\u0448\u0435\u043c \u0441\u043b\u0443\u0447\u0430\u0435 \u043c\u044b \u0432\u044b\u0431\u0440\u0430\u043b\u0438 \u043c\u0435\u0442\u043e\u0434 \u0430\u043d\u0430\u043b\u0438\u0437\u0430 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432, \u043f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u043e\u043d \u043c\u0435\u043d\u0435\u0435 \u0442\u0440\u0443\u0434\u043e\u0451\u043c\u043a\u0438\u0439 \u0438 \u043c\u0438\u043d\u0438\u043c\u0430\u043b\u044c\u043d\u043e \u0432\u043b\u0438\u044f\u0435\u0442 \u043d\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b. \u0412\u0430\u0436\u043d\u043e \u043e\u0442\u043c\u0435\u0442\u0438\u0442\u044c, \u0447\u0442\u043e \u043b\u044e\u0431\u043e\u0439 \u0441\u0431\u043e\u0440 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0434\u0430\u043d\u043d\u044b\u0445 \u0441\u043e\u0437\u0434\u0430\u0451\u0442 \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u0443\u044e \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0443 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440. \u041a\u043b\u044e\u0447\u0435\u0432\u043e\u0439 \u0432\u043e\u043f\u0440\u043e\u0441 \u0441\u043e\u0441\u0442\u043e\u0438\u0442 \u0432 \u043e\u0431\u044a\u0451\u043c\u0435 \u0438 \u0445\u0430\u0440\u0430\u043a\u0442\u0435\u0440\u0435 \u044d\u0442\u043e\u0439 \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0438, \u0430 \u0442\u0430\u043a\u0436\u0435 \u0435\u0451 \u0432\u043b\u0438\u044f\u043d\u0438\u0438 \u043d\u0430 \u043e\u0431\u0449\u0443\u044e \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b.<\/p>\n<pre><code class=\"sql\">SELECT      OBJECT_NAME(ius.object_id) AS TableName,     i.name AS IndexName,     i.type_desc AS IndexType,     ius.user_seeks + ius.user_scans + ius.user_lookups AS TotalUserReads,     ius.user_updates AS TotalUserWrites,     ius.last_user_seek,     ius.last_user_scan,     ius.last_user_lookup FROM      sys.dm_db_index_usage_stats ius INNER JOIN      sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id WHERE      OBJECTPROPERTY(ius.object_id, 'IsMsShipped') = 0 -- \u0438\u0441\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u043d\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b     AND ius.database_id = DB_ID() -- \u043f\u0440\u043e\u0432\u0435\u0440\u0438\u043c \u0442\u043e\u043b\u044c\u043a\u043e \u0442\u043a\u0443\u0449\u0443\u044e \u0411\u0414 ORDER BY      TotalUserReads DESC;<\/code><\/pre>\n<h4>\u041c\u0430\u0440\u043a\u0438\u0440\u043e\u0432\u043a\u0430 \u0442\u0430\u0431\u043b\u0438\u0446<\/h4>\n<p>\u041f\u043e\u0441\u043b\u0435 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0438\u044f <strong>\u0433\u043e\u0440\u044f\u0447\u0438\u0445 \u0442\u0430\u0431\u043b\u0438\u0446<\/strong>, \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u043e\u0442\u043c\u0435\u0442\u0438\u0442\u044c \u0438\u0445 \u0434\u043b\u044f \u0434\u0430\u043b\u044c\u043d\u0435\u0439\u0448\u0435\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0438. \u0412 \u043d\u0430\u0448\u0435\u043c \u0441\u043b\u0443\u0447\u0430\u0435 \u0440\u0435\u0448\u0435\u043d\u043e \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c \u0440\u0430\u0441\u0448\u0438\u0440\u0435\u043d\u043d\u043e\u0435 \u0441\u0432\u043e\u0439\u0441\u0442\u0432\u043e <strong>&#171;IsHotTable&#187;<\/strong><\/p>\n<p>\u0414\u043b\u044f \u0443\u0434\u043e\u0431\u0441\u0442\u0432\u0430 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f, \u044f \u043e\u0444\u043e\u0440\u043c\u0438\u043b \u043a\u043e\u0434 \u0432 \u0432\u0438\u0434\u0435 \u0445\u0440\u0430\u043d\u0438\u043c\u043e\u0439 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u044b<\/p>\n<blockquote>\n<p>\u2705 \u0414\u043b\u044f \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0440\u0430\u0431\u043e\u0442\u044b \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0443\u0435\u0442\u0441\u044f \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u0443\u044e \u0441\u0445\u0435\u043c\u0443 \u0432 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445, \u0432 \u0440\u0430\u043c\u043a\u0430\u0445 \u043a\u043e\u0442\u043e\u0440\u043e\u0439 \u0431\u0443\u0434\u0443\u0442 \u0440\u0430\u0437\u043c\u0435\u0449\u0430\u0442\u044c\u0441\u044f \u0432\u0441\u0435 \u043e\u0431\u044a\u0435\u043a\u0442\u044b \u0434\u043b\u044f \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u044f \u0411\u0414, \u0442\u0430\u043a\u0438\u0435 \u043a\u0430\u043a \u0445\u0440\u0430\u043d\u0438\u043c\u044b\u0435 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u044b \u0438\u043b\u0438 \u0441\u043b\u0443\u0436\u0435\u0431\u043d\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b.<\/p>\n<\/blockquote>\n<pre><code class=\"sql\">IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'DBA') BEGIN     EXEC('CREATE SCHEMA DBA');     PRINT 'Schema [DBA] created successfully.'; END ELSE BEGIN     PRINT 'Schema [DBA] already exists.'; END<\/code><\/pre>\n<pre><code class=\"sql\">CREATE PROCEDURE dba.SetTableExtendedProperty     @TableName SYSNAME,     @SchemaName SYSNAME = 'dbo',     @PropertyName SYSNAME,     @PropertyValue NVARCHAR(4000) AS BEGIN     SET NOCOUNT ON;      DECLARE @SQL NVARCHAR(MAX);      -- \u041f\u0440\u043e\u0432\u0435\u0440\u0438\u0442\u044c \u043d\u0430\u043b\u0438\u0447\u0438\u0435 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u0430     IF EXISTS (         SELECT 1         FROM fn_listextendedproperty(@PropertyName, 'SCHEMA', @SchemaName, 'TABLE', @TableName, NULL, NULL)     )     BEGIN         -- \u0415\u0441\u043b\u0438 \u0438\u043c\u0435\u0435\u0442\u0441\u044f,  \u0442\u043e\u0433\u0434\u0430 \u043d\u0443\u0436\u043d\u043e \u0443\u0434\u0430\u043b\u0438\u0442\u044c \u0438 \u043f\u0435\u0440\u0435\u0441\u043e\u0437\u0434\u0430\u0442\u044c          SET @SQL = '         EXEC sp_dropextendedproperty              @name = N''' + @PropertyName + ''',              @level0type = N''SCHEMA'',              @level0name = N''' + @SchemaName + ''',              @level1type = N''TABLE'',              @level1name = N''' + @TableName + ''';';          EXEC sp_executesql @SQL;     END      -- \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043d\u043e\u0432\u044b\u0439     SET @SQL = '     EXEC sp_addextendedproperty          @name = N''' + @PropertyName + ''',          @value = N''' + REPLACE(@PropertyValue, '''', '''''') + ''',          @level0type = N''SCHEMA'',          @level0name = N''' + @SchemaName + ''',          @level1type = N''TABLE'',          @level1name = N''' + @TableName + ''';';      EXEC sp_executesql @SQL;      PRINT 'Extended property [' + @PropertyName + '] updated for [' + @SchemaName + '].[' + @TableName + ']'; END;<\/code><\/pre>\n<blockquote>\n<p>\u261d\ud83c\udffc\u0412\u0430\u0436\u043d\u043e \u043f\u043e\u043c\u043d\u0438\u0442\u044c, \u0447\u0442\u043e &#171;\u0433\u043e\u0440\u044f\u0447\u0438\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b&#187; \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u044f\u044e\u0442\u0441\u044f \u043d\u0435 \u0442\u043e\u043b\u044c\u043a\u043e \u043f\u043e \u0447\u0430\u0441\u0442\u043e\u0442\u0435 \u043e\u0431\u0440\u0430\u0449\u0435\u043d\u0438\u0439, \u043d\u043e \u0438 \u043f\u043e \u0438\u0445 \u0432\u0430\u0436\u043d\u043e\u0441\u0442\u0438 \u0432 \u0431\u0438\u0437\u043d\u0435\u0441-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430\u0445. \u0420\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0443\u0435\u0442\u0441\u044f \u0441\u043e\u0441\u0442\u0430\u0432\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0442\u0430\u043a\u0438\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u0432\u0440\u0443\u0447\u043d\u0443\u044e, \u043f\u043e\u0441\u043b\u0435 \u0447\u0435\u0433\u043e \u043f\u0440\u043e\u0438\u0437\u0432\u0435\u0441\u0442\u0438 \u0438\u0445 \u043c\u0430\u0440\u043a\u0438\u0440\u043e\u0432\u043a\u0443 \u0432 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445.<\/p>\n<\/blockquote>\n<p>\u041f\u0440\u0438\u043c\u0435\u0440 \u043c\u0430\u0440\u043a\u0438\u0440\u043e\u0432\u043a\u0438<\/p>\n<pre><code class=\"sql\">EXEC dba.SetTableExtendedProperty     @TableName = 'Customers',     @SchemaName = 'dbo',     @PropertyName = 'IsHotTable',     @PropertyValue = '1';<\/code><\/pre>\n<p><strong>\u041f\u0440\u0438\u043c\u0435\u0440<\/strong>: \u041a\u0430\u043a \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0441\u043f\u0438\u0441\u043e\u043a \u0441 \u043c\u0430\u0440\u043a\u0438\u0440\u043e\u0432\u043a\u043e\u0439 <strong>IsHotTable<\/strong><\/p>\n<pre><code class=\"sql\">SELECT t.name   AS TableName,        ep.name  AS PropertyName,        ep.value AS PropertyValue FROM sys.tables t          CROSS APPLY      fn_listextendedproperty(default, 'SCHEMA', SCHEMA_NAME(t.schema_id), 'TABLE', t.name, NULL, NULL) ep WHERE t.is_ms_shipped = 0   and ep.name = 'IsHotTable'   and ep.value = '1';<\/code><\/pre>\n<h4>\u041e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435 \u0433\u043e\u0440\u044f\u0447\u0438\u0445 \u0442\u0430\u0431\u043b\u0438\u0446<\/h4>\n<p>\u0412 \u0440\u0430\u043c\u043a\u0430\u0445 \u043d\u0430\u0448\u0435\u0439 \u0441\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u0438 \u043f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u0435\u0442\u0441\u044f \u0435\u0436\u0435\u0434\u043d\u0435\u0432\u043d\u043e \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0439 \u0432\u0438\u0434 \u0442\u0430\u0431\u043b\u0438\u0446, \u043d\u0435\u0441\u043c\u043e\u0442\u0440\u044f \u043d\u0430 \u0443\u0440\u043e\u0432\u043d\u0438 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438.<\/p>\n<p>\u0414\u043b\u044f \u0441\u043e\u043a\u0440\u0430\u0449\u0435\u043d\u0438\u044f \u0432\u0440\u0435\u043c\u0435\u043d\u0438 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438 \u0431\u0443\u0434\u0435\u043c \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u043c\u043d\u043e\u0433\u043e\u043f\u043e\u0442\u043e\u0447\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434. \u0422\u0430\u0431\u043b\u0438\u0446\u044b \u0431\u0443\u0434\u0443\u0442 \u0440\u0430\u0437\u0434\u0435\u043b\u0435\u043d\u044b \u043d\u0430 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0433\u0440\u0443\u043f\u043f \u0432 \u0437\u0430\u0432\u0438\u0441\u0438\u043c\u043e\u0441\u0442\u0438 \u043e\u0442 \u0434\u043e\u0441\u0442\u0443\u043f\u043d\u044b\u0445 \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0432 \u0441\u0438\u0441\u0442\u0435\u043c\u044b. \u041a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e \u0433\u0440\u0443\u043f\u043f \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u043e \u0432 \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0438\u0438 \u0441 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e \u0441\u0435\u0440\u0432\u0435\u0440\u0430.<\/p>\n<p><strong>\u041e\u043f\u0438\u0441\u0430\u043d\u0438\u0435 \u0441\u0438\u0441\u0442\u0435\u043c\u044b \u043c\u043d\u043e\u0433\u043e\u043f\u043e\u0442\u043e\u0447\u043d\u043e\u0433\u043e \u043f\u043e\u0434\u0445\u043e\u0434\u0430<\/strong><\/p>\n<p>\u0421\u0438\u0441\u0442\u0435\u043c\u0430 \u0440\u0435\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u043d\u0430 \u0441 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435\u043c \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u043e\u0433\u043e \u043c\u0435\u0445\u0430\u043d\u0438\u0437\u043c\u0430 \u0421\u0423\u0411\u0414 &#8212; SQL Agent Job, \u0432 \u0440\u0430\u043c\u043a\u0430\u0445 \u043a\u043e\u0442\u043e\u0440\u043e\u0433\u043e \u0441\u043e\u0437\u0434\u0430\u0435\u0442\u0441\u044f \u0433\u043b\u0430\u0432\u043d\u043e\u0435 \u0437\u0430\u0434\u0430\u043d\u0438\u0435 (Master Job), \u043a\u043e\u0442\u043e\u0440\u043e\u0435 \u043e\u0442\u0432\u0435\u0447\u0430\u0435\u0442 \u0437\u0430 \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u0438 \u0443\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u0435 \u043f\u043e\u0434\u0437\u0430\u0434\u0430\u043d\u0438\u044f\u043c\u0438 (\u043f\u043e\u0442\u043e\u043a\u0430\u043c\u0438).<\/p>\n<p>Master Job \u0441\u043e\u0441\u0442\u043e\u0438\u0442 \u0438\u0437 2 \u0448\u0430\u0433\u043e\u0432:<\/p>\n<ul>\n<li>\n<p>\u0428\u0430\u0433 \u21161 &#8212; \u0421\u043e\u0437\u0434\u0430\u0435\u0442 \u0434\u0438\u043d\u0430\u043c\u0438\u0447\u0435\u0441\u043a\u0438\u0435 \u043f\u043e\u0434\u0437\u0430\u0434\u0430\u043d\u0438\u044f \u0432 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0435, \u0440\u0430\u0432\u043d\u043e\u043c \u0447\u0438\u0441\u043b\u0443 \u043f\u043e\u0442\u043e\u043a\u043e\u0432.<\/p>\n<\/li>\n<li>\n<p>\u0428\u0430\u0433 \u21162 &#8212; \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u0435\u0442 \u0441\u043e\u0437\u0434\u0430\u043d\u043d\u044b\u0435 \u043f\u043e\u0434\u0437\u0430\u0434\u0430\u043d\u0438\u044f \u0438 \u0443\u0434\u0430\u043b\u044f\u0435\u0442 \u0438\u0445 \u043f\u043e\u0441\u043b\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f<\/p>\n<\/li>\n<\/ul>\n<p>\u0414\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u043f\u043e\u0434\u0441\u0438\u0441\u0442\u0435\u043c\u0443 \u043b\u043e\u0433\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0434\u043b\u044f \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u043d\u0438\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u043c\u0435\u0445\u0430\u043d\u0438\u0437\u043c\u0430 \u043c\u043d\u043e\u0433\u043e\u043f\u043e\u0442\u043e\u0447\u043d\u043e\u0439 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438.<\/p>\n<p><strong>\u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u043f\u043e\u0434\u0441\u0438\u0441\u0442\u0435\u043c\u044b \u043b\u043e\u0433\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f<\/strong><\/p>\n<p>\u0414\u043b\u044f \u043d\u0430\u0447\u0430\u043b\u0430 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0434\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u043b\u043e\u0433\u043e\u0432.<\/p>\n<pre><code class=\"sql\">SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [DBA].[ReindexJobLog]( [LogID] [int] IDENTITY(1,1) NOT NULL, [JobName] [nvarchar](128) NULL, [GroupNumber] [int] NULL, [Tablename] [sysname] NOT NULL, [IndexName] [nvarchar](128) NULL, [ActionType] [nvarchar](128) NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [DurationSeconds]  AS (datediff(second,[StartTime],[EndTime])), [DBName] [nvarchar](128) NULL, PRIMARY KEY CLUSTERED  ( [LogID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FIVE] ) ON [FIVE] GO  ALTER TABLE [DBA].[ReindexJobLog] ADD  DEFAULT (getdate()) FOR [StartTime] GO   <\/code><\/pre>\n<p>\u0414\u043b\u044f \u0431\u043e\u043b\u0435\u0435 \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0439 \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u043a\u043e\u0434\u0430 \u0438 \u0443\u043f\u0440\u043e\u0449\u0435\u043d\u0438\u044f \u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u044b \u0437\u0430\u0434\u0430\u043d\u0438\u044f (SQL Job), \u0441\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u0443\u044e \u0445\u0440\u0430\u043d\u0438\u043c\u0443\u044e \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0443 \u0434\u043b\u044f \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430 \u043f\u0435\u0440\u0435\u0438\u043d\u0434\u0435\u043a\u0441\u0430\u0446\u0438\u0438.<\/p>\n<blockquote>\n<p>\ud83d\udea8\u041d\u0435 \u0437\u0430\u0431\u0443\u0434\u044c\u0442\u0435 \u0438\u0437\u043c\u0435\u043d\u0438\u0442\u044c \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u0435 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u0441\u043a\u0440\u0438\u043f\u0442\u0435<\/p>\n<\/blockquote>\n<pre><code class=\"sql\">SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO  Create PROCEDURE [DBA].[ReindexTableWithLogging] @JobName NVARCHAR(128),                                                 @GroupNumber INT,                         @DBName Nvarchar(128),                                                 @TableName SYSNAME,                                                 @SchemaName SYSNAME = 'dbo' AS BEGIN     SET NOCOUNT ON;     DECLARE @FullTableName NVARCHAR(256) = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);     DECLARE @LogID INT;     DECLARE @indexName NVARCHAR(256);     Declare @sql_updateStats NVARCHAR(max) DECLARE @Batch1_SQL NVARCHAR(MAX);     DECLARE @Batch2_SQL NVARCHAR(MAX);     DECLARE @Batch3_SQL NVARCHAR(MAX);     DECLARE IndexCursor CURSOR LOCAL FAST_FORWARD FOR         SELECT i.name AS IndexName         FROM sys.indexes i         WHERE i.name IS NOT NULL -- Exclude heaps           and object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))     OPEN IndexCursor;     FETCH NEXT FROM IndexCursor INTO @IndexName;     WHILE @@FETCH_STATUS = 0         begin             INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, Tablename, Indexname, ActionType, StartTime)             VALUES (@JobName, @GroupNumber, @DBName, @FullTableName, @indexName, 'ReBuild Index All indexes', GETDATE());             SET @LogID = SCOPE_IDENTITY();             PRINT 'Starting reindex for group ' + CAST(@GroupNumber AS NVARCHAR(10)) + '\\Table' + @FullTableName +                   ' Index ' + @indexname + ' at ' + CONVERT(NVARCHAR, GETDATE(), 120);             -- Run reindex             EXEC ('DBCC DBREINDEX(''' + @FullTableName + ''', ''' + @indexname + ''', 90);');             -- Update log             UPDATE [DatabaseName].DBA.ReindexJobLog             SET EndTime = GETDATE()             WHERE LogID = @LogID               and Tablename = @FullTableName               and indexname = @indexName;             PRINT 'Finished reindexing group ' + CAST(@GroupNumber AS NVARCHAR(10)) + '\\Table' + @FullTableName +                   ' at ' + CONVERT(NVARCHAR, GETDATE(), 120);             FETCH NEXT FROM IndexCursor INTO @IndexName;         END     INSERT<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-471627","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/471627","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=471627"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/471627\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=471627"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=471627"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=471627"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}