SqlServer e parallelismo, la chiave per incrementare le performance in pochi secondi

Nelle configurazioni di SQLServer c’è una sezione denominata Parallelismo.

Questa sezione contiene due chiavi che di default sono impostate con dei parametri eccessivamente aggressivi, e che non danno beneficio nella maggior parte dei casi. Agendo su queste due chiavi, si può migliorare le perfomance senza operare complesse attività di tuning.

  1. Massimo grado di parallelismo (Maximum Degree of Parallelism) è impostato di default a 0, il che significa senza limiti. Fa da se che SqlServer utilizza tutte le CPU disponibili durante l’esecuzione di una query. Se eseguiamo esclusivamente query molto pesanti e su un grandissimo numero di dati, questa configurazione è corretta, ma in un uso applicativo, questo approccio limita la concorrenza, creando dei colli di bottiglia. E’ preferibile limitare il parallelismo dai un numero di socket che permetta un livello di concorrenza sostenibile.

    Ad esempio, un una configurazione con 4 core, sentiamo il valore a 2.

  2. Costo soglia per parallelismo (Cost Threshold for Parallelism) è impostato di default a 5. E’ un valore un pò troppo basso, che induce l’ottimizzatole a valutare piani alternativi di esecuzione che possano utilizzare più thread contemporaneamente.

Anche questa impostazione è ideale per applicazioni di BI o OLTP, ma totalmente inadeguato per applicazioni con IO dati generico, come CRM e gestionali.

Va assolutamente aumentato ad almeno 30, ma c’è chi consiglia di portarlo anche a 50.

Personalmente ho riscontrato un beneficio immediato da un cliente che lamentava rallentamenti, con un crollo del carico della CPU dal 70% al 30% e un immediato raddoppio delle operazioni batch eseguite al secondo. Un must per l’ottimizzazione delle performance.

Sono delle impostazioni poco note ai più, ma fidatevi che avrete un server molto più performante con un operazione di pochi secondi.

E venne il giorno… the database principal owns a schema in the database and cannot be dropped

E’ da qualche settimana che procastino la migrazione dei sistemi su SqlServer 2014 e questo weekend è risultato la scelta migliore anche grazie a all’accavallamento con il 1° maggio che ha garantito traffico zero (o quasi) sui nostri sistemi.

Spostare i DB non è di per se un grande problema, ma per ridurre al dba2minimo il tempo di “down” su alcuni servizi minori non ridondati, nel fare i test ho riscontrato qualche difficoltà nel rimappare schemi e utenti, che anche a parità di nome e password non corrispondono comunque una volta che il DB viene riconnesso.

Per evitare di ricollegare tanti piccoli DB a mano cambiano schemi e utenti, mi sono imbattuto in un’utilissima Stored Procedure che semplifica di molto la vita.

Infatti non basta “droppare” l’utente, perché se c’è uno schema collegato, l’operazione è ben più complicata e tediosa, e riceveremo errori del tipo “the database principal owns a schema in the database and cannot be dropped“.

Invece utilizzando questa stored procedure “Script to Drop All Orphaned SQL Server Database Users” l’operazione diventa estremamente rapita, e può essere lanciata su tutti i DB ricollegati in un colpo solo con il comando:

EXEC sp_msforeachdb 'USE [?]; EXEC sp_Drop_OrphanedUsers'

Un time saver eccezionale, se dovete fare una migrazione massiva.

Copio la stored qualora il sito dovesse scomparire:

use [master]
go
create proc dbo.sp_Drop_OrphanedUsers
as
begin
set nocount on
-- get orphaned users
declare @user varchar(max)
declare c_orphaned_user cursor for
select name
from sys.database_principals
where type in ('G','S','U')
and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') )
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser') open c_orphaned_user
fetch next from c_orphaned_user into @user
while(@@FETCH_STATUS=0)
begin
-- alter schemas for user
declare @schema_name varchar(max)
declare c_schema cursor for
select name from sys.schemas where USER_NAME(principal_id)=@user
open c_schema
fetch next from c_schema into @schema_name
while (@@FETCH_STATUS=0)
begin
declare @sql_schema varchar(max)
select @sql_schema='ALTER AUTHORIZATION ON SCHEMA::['+@schema_name+ '] TO [dbo]'
print @sql_schema
exec(@sql_schema)
fetch next from c_schema into @schema_name
end
close c_schema
deallocate c_schema

-- alter roles for user
declare @dp_name varchar(max)
declare c_database_principal cursor for
select name from sys.database_principals
where type='R' and user_name(owning_principal_id)=@user
open c_database_principal
fetch next from c_database_principal into @dp_name
while (@@FETCH_STATUS=0)
begin
declare @sql_database_principal varchar(max)
select @sql_database_principal ='ALTER AUTHORIZATION ON ROLE::['+@dp_name+ '] TO [dbo]'
print @sql_database_principal
exec(@sql_database_principal )
fetch next from c_database_principal into @dp_name
end
close c_database_principal
deallocate c_database_principal

-- drop roles for user
declare @role_name varchar(max)
declare c_role cursor for
select dp.name--,USER_NAME(member_principal_id)
from sys.database_role_members drm
inner join sys.database_principals dp
on dp.principal_id= drm.role_principal_id
where USER_NAME(member_principal_id)=@user
open c_role
fetch next from c_role into @role_name
while (@@FETCH_STATUS=0)
begin
declare @sql_role varchar(max)
select @sql_role='EXEC sp_droprolemember N'''+@role_name+''', N'''+@user+''''
print @sql_role
exec (@sql_role)
fetch next from c_role into @role_name
end
close c_role
deallocate c_role

-- drop user
declare @sql_user varchar(max)
set @sql_user='DROP USER ['+@user +']'
print @sql_user
exec (@sql_user)
fetch next from c_orphaned_user into @user
end
close c_orphaned_user
deallocate c_orphaned_user
set nocount off
end
go
-- mark stored procedure as a system stored procedure
exec sys.sp_MS_marksystemobject sp_Drop_OrphanedUsers
go

MySql LIMIT in SqlServer

Quando si sviluppano dei siti che devono rappresentare un gran numero dati, l'unica soluzione per non generare tabelle enormi è l'uso della paginazione.

CI sono molti modi di paginare, ma il più comune e utilizzando delle stored procedure, specialmente in SqlServer. [more]

MySql offre una soluzione molto interessante attraverso il comando LIMIT, dove oltre a passare la il numero di righe che voglio ricevere (cosa che in SqlServer posso fare con il comando TOP) devo specificare l'offset di inizio. Quindi il comando LIMIT 15,5 sestituirà i risultati da 16 a 20.>

Ed ora arriviamo al punto di questo post: come posso fare a replicare questo comando in SqlServer?

Dopo innumerevoli tentativi, il sistema più rapido e che offre il miglior rapporto carico/prestazioni sembra essere la seguente query:

SELECT TOP x * FROM table
WHERE id NOT IN (SELECT TOP y id FROM table ORDER BY id)
ORDER BY id;

dove:

"id" è la chiave primaria della tabella
"x" è il numero di risultati che vlglio ottenere
"y" è la riga di partenza o offset della mia query

Il risultato è identico, peccato che la sintassi è completamente diversa e comporta precchi problemi se si vuole usare lo stesso codice sia in SqlServer che in MySql.

Qualcuno è riuscito a emulare LIMIT in tutto e per tutto con un UDF o un Stored?