PowerShell analyzes the correlation between the fields to be created by SQL Server and the existing indexes
This paper introduces how to analyze the correlation between an index to be created and an existing index from the table level.
Briefly analyze what aspects to consider in creating an index:
1. Related sql statements
2. Table
3. The influence of performance
Steps:
1. Analyze the fields extracted from the table and index from the relevant SQL statements
2. Combined with the actual situation of the table (existing indexes, data density, hot table types, etc.), determine the fields of the index and whether it is suitable to create
3. If it is suitable for creation, analyze and compare the performance differences before and after creation
(inadequacies, make up by your own brain):
Let the PowerShell code implement an idea similar to that of a DBA (myself) to analyze the data density of point 2 and the existing index
1. Data density
Data density refers to the number of records with unique key value, that is, data density = 1 / number of records with unique key value. When the data density is smaller, that is, the uniqueness of key value is higher, it means that the field is more suitable for indexing.
PowerShell implements fields to be sorted by data density from small to large:
Function sort_index_columns ($server,$db_name,$table_name,$in_index) {
If ($in_index.Contains (',')) {
$in_index_arr=$in_index.split (',')
}
Else {
$in_index_arr=@ ($in_index)
}
$column_arr=@ ()
$density_arr=@ ()
Foreach ($column in $in_index_arr) {
$indexes1=invoke-sqlcmd "use $db_name;select count (distinct $column) as count from $table_name with (nolock)"-ServerInstance $server
$column_arr+=$column
$density_arr+=$indexes1.count
}
$list=$density_arr | Sort-Object
$str=''
For ($naughty list.murf 1-ge 0-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:
Before sorting: sku,shipmentID,PackageNo,AsnNo
After sorting: PackageNo,shipmentID,AsnNo,sku
2. Index analysis
Compare the fields of the index to be built with the existing indexes, analyze and compare the fields and the corresponding order, and judge the common fields of the existing 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 {
$in_index_arr=@ ($in_index)
}
$db_indexes_used_arr=@ ()
$db_indexes_unused_arr=@ ()
$db_indexes_serial_arr=@ ()
$indexes1=invoke-sqlcmd "use $db_name;Exec sp_helpindex $table_name"-ServerInstance $server
For ($n / o / n / n-lt $indexes1.length;$n=$n+1) {
$index2_same_arr=@ ()
$index1_same_arr=@ ()
$new_arr=@ ()
$same_arr=@ ()
$str=''
# # removing spaces and (-) between index fields
$idx_1_tmp=$indexes1 [$n] .index _ keys-replace',''
$idx_1=$idx_1_tmp-replace'\ (-\)',''
# # splitting Index Fields into arrays
If ($idx_1.Contains (',')) {
$idx_1_arr=$idx_1.split (',')
$idx_1_size=$idx_1_arr.length
}
Else {
$idx_1_size=1
$idx_1_arr=@ ($idx_1)
}
# # compare the number of fields in the two indexes
If ($idx_1_size-gt $in_index_arr.length) {
For ($x / x / s / x-lt $in_index_arr.length;$x=$x+1) {
# # record the number of fields with the same index
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
}
# # record the matching position of two indexes
$index2_same_arr+=$x
$index1_same_arr+=$xx
}
}
}
}
Else {
For ($yaw0witch / 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
}
# # record the matching position of two indexes
$index1_same_arr+=$y
$index2_same_arr+=$yy
}
}
}
}
If ($index1_same_arr [0]-eq 0) {
# # get the matching fields in order
For ($zong0witz-lt $index1_same_arr.length;$z++) {
If ($z-eq $index1_same_arr [$z]) {
$new_arr+=$in_index_arr [$index2_same_arr [$z]]
$count=$z
}
}
$db_indexes_serial_arr+=$count+1
# # Index fields to be built minus matched fields
$diff_arr=Compare-Object-ReferenceObject $in_index_arr-DifferenceObject $new_arr | Select-Object-ExpandProperty InputObject
$new_index=$new_arr+$diff_arr
# # reorganization of Index Fields to be built
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+= "$($indexes1 [$n] .index _ name) ($idx_1)"
} else {
$db_indexes_serial_arr+=0
$db_indexes_unused_arr+= "$($indexes1 [$n] .index _ name) ($idx_1)"
}
}
Echo "Table: $table_name"
Echo "Fields to be indexed: $in_index"
Echo "Index involving related fields:"
Foreach ($an in $db_indexes_used_arr) {
Echo $a
}
Echo "extraneous index:"
Foreach ($b in $db_indexes_unused_arr) {
Echo $b
}
}
Test results:
Note: the index to be built is an index that has not yet been created, and the order of fields is adjustable.
Call code:
$server='' # # instance
$db_name='' # # Database
$table_name='' # # Table
$in_index='' # # Index field, multiple fields separated by commas
$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
From the test results, we can successfully determine the existing indexes related to the index to be built, and we no longer have to query and sp_helpindex table_name in the database, but to be a "lazy" DBA.
