PowerShell parses the correlation between the fields of the SQL Server to be indexed and the existing indexes

This article describes how to analyze the correlation between an index to be created and an existing index from a table level

A brief analysis of what to consider when creating an index:

1. Related SQL statements

2. Table

3, the impact of performance

Steps:

1. Analyze the fields of the extracted table and index from the related SQL statement

2, combined with the actual situation of the table (index, data density, hotspot table type, etc.), determine the field of the index and whether it is appropriate to create

3, if it is suitable to create, analyze and compare the performance difference before and after creation

(deficiency, self -650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0004.gif "alt=" J_0004.gif "/>":

Let PowerShell code implement similar to a DBA(myself 650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0019.gif "alt=" j_ 0019.gif "/>"thinking to analyze the 2nd data density and the existing index

1. Data density

Data density is one of the unique records of the key value, that is: The data density =1/the unique number of records, when the data density is smaller, that is, the higher the uniqueness of the key value, the more appropriate to index the field.

The PowerShell implementation fields are sorted from small to large by data density:

function Sort_index_columns ($server, $db _name, $table _name, $in _index) {

if ($in _index. Contains (', ')) {

$in _index_arr= $in _index.split (', ')

}

else{

[Email protected] ($in _index)

}

[Email protected] ()

[Email protected] ()

foreach ($column in $in _index_arr) {

$indexes 1=invoke-sqlcmd "Use $db _name;select count (distinct $column) as count from $table _name with (nolock)"- ServerInstance $server

$column _arr+= $column

$density _arr+= $indexes 1.count

}

$list = $density _arr | Sort-object

$str = "

for ($n = $list. length-1; $n-ge 0; $n = $n-1) {

$num = $density _arr.indexof ([int] $list [$n])

if ($n-gt 0) {

$str + = $column _arr[$num]+ ', '}

else{

$str + = $column _arr[$num]

}

}

Return $STR

}

Test results:

650) this.width=650; "Src=" Https://s3.51cto.com/oss/201710/26/f4e318749631fd7f1ba3a79b70ed3ad6.png-wh_500x0-wm_3 -wmp_4-s_3614710700.png "title=" sort. PNG "alt=" F4e318749631fd7f1ba3a79b70ed3ad6.png-wh_ "/>

Before sorting: Sku,shipmentid,packageno,asnno

After sorting: Packageno,shipmentid,asnno,sku

2. Index Analysis

The fields to be indexed are compared to existing indexes, which are analyzed from the fields and the corresponding order to determine the common fields of the index and the index to be built.

function Index_analysis ($server, $db _name, $table _name, $in _index) {

if ($in _index. Contains (', ')) {

$in _index_arr= $in _index.split (', ')

}

else{

[Email protected] ($in _index)

}

[Email protected] ()

[Email protected] ()

[Email protected] ()

$indexes 1=invoke-sqlcmd "Use $db _name; Exec sp_helpindex $table _name "-serverinstance $server

for ($n =0; $n-lt $indexes 1.length; $n = $n + 1) {

[Email protected] ()

[Email protected] ()

[Email protected] ()

[Email protected] ()

$str = "

# #去掉索引字段之间的空格及 (-)

$idx _1_tmp= $indexes 1[$n].index_keys-replace ","

$idx _1= $idx _1_tmp-replace ' \ (-\) ', '

# #索引字段拆分成数组

if ($idx _1.contains (', ')) {

$idx _1_arr= $idx _1.split (', ')

$idx _1_size= $idx _1_arr.length

}

else{

$idx _1_size=1;

[Email protected] ($idx _1)

}

# #对比两个索引的字段个数

if ($idx _1_size-gt $in _index_arr.length) {

for ($x =0; $x-lt $in _index_arr.length; $x = $x + 1) {

# #记录两个索引相同的字段个数

for ($xx =0; $xx-lt $idx _1_size; $xx +=1) {

if ($in _index_arr[$x]-eq $idx _1_arr[$xx]) {

if ($x-eq $xx) {

$same _arr+= $x

}

# #记录两个索引的匹配位置

$index 2_same_arr+= $x

$index 1_same_arr+= $xx

}

}

}

}

else{

for ($y =0; $y-lt $idx _1_size; $y = $y + 1) {

for ($yy =0; $yy-lt $in _index_arr.length; $yy +=1) {

if ($idx _1_arr[$y]-eq $in _index_arr[$yy]) {

if ($y-eq $yy) {

$same _arr+= $y

}

# #记录两个索引的匹配位置

$index 1_same_arr+= $y

$index 2_same_arr+= $yy

}

}

}

}

if ($index 1_same_arr[0]-eq 0) {

# #按顺序取匹配到的字段

for ($z =0; $z-lt $index 1_same_arr.length; $z + +) {

if ($z-eq $index 1_same_arr[$z]) {

$new _arr+= $in _index_arr[$index 2_same_arr[$z]

$count = $z

}

}

$db _indexes_serial_arr+= $count +1

# #待建索引字段减去已匹配字段

$diff _arr=compare-object-referenceobject $in _index_arr-differenceobject $new _arr | Select-object-expandproperty Inputobject

$new _index= $new _arr+ $diff _arr

# #待建索引字段重组

for ($zz =0; $zz-lt $new _index.length; $zz + +) {

if ($zz-lt $new _index.length-1) {

$str + = $new _index[$zz]+ ', '

}

else{

$str + = $new _index[$zz]

}

}

$db _indexes_used_arr+= "$ ($indexes 1[$n].index_name) ($idx _1)"

}else{

$db _indexes_serial_arr+=0

$db _indexes_unused_arr+= "$ ($indexes 1[$n].index_name) ($idx _1)"

}

}

echo "Table: $table _name"

echo "field to create index: $in _index"

echo "involves the index of the related field:"

foreach ($a in $db _indexes_used_arr) {

Echo $a

}

echo "Unrelated index:"

foreach ($b in $db _indexes_unused_arr) {

Echo $b

}

}

Test results:

650) this.width=650; "Src=" Https://s1.51cto.com/oss/201710/26/cc47902f3dd1c199fcfe1d88259ad747.png-wh_500x0-wm_3 -wmp_4-s_2381678252.png "title=" PowerShell index comparison. png "alt=" Cc47902f3dd1c199fcfe1d88259ad747.png-wh_ "/>

Note: The index to be built is an index that has not yet been created, and the field order is adjustable

Calling code:

$server = ' # #实例

$db _name= ' # #数据库

$table _name= ' # #表

$in _index= ' # #索引字段, multiple fields with a comma interval

$sort _index=sort_index_columns $server $db _name $table _name $in _index

echo "before sorting: $in _index" "After sorting: $sort _index"

Index_analysis $server $db _name $table _name $sort _index

Judging from the results of the test, the successful determination of the index associated with the indexes to be built, no longer have to go to the database inside the query and their own judgment (Sp_helpindex table_name), to be a "lazy" dba650) this.width=650 ; "Src=" Http://img.baidu.com/hi/jx2/j_0003.gif "alt=" J_0003.gif "/>

This article is from the "Aimax" blog, make sure to keep this source http://aimax.blog.51cto.com/11610508/1976456

PowerShell parses the correlation between the fields of the SQL Server to be indexed and the existing indexes