Category Archives: Database

SQL 2014 LocalDB: Instance “v12.0” není automaticky založena (50 – Local Database Runtime error occurred. The specified LocalDB instance does not exist.)

Mám na počítači čerstvou instalaci SQL Serveru 2014 (včetně LocalDB) a hraju si s Entity Frameworkem 6.1.0, Výchozí konektivitou je “(localdb)\v12.0” a dostávám krásnou chybu:

System.Data.Entity.Core.ProviderIncompatibleException, but exception System.Data.Entity.Validation.DbEntityValidationException was expected. Exception message: System.Data.Entity.Core.ProviderIncompatibleException: An error occurred accessing the database. This usually means that the connection to the database failed. Check that the connection string is correct and that the appropriate DbContext constructor is being used to specify it or find it in the application’s config file. See http://go.microsoft.com/fwlink/?LinkId=386386 for information on DbContext and connections. See the inner exception for details of the failure. —> System.Data.Entity.Core.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string. —> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 – Local Database Runtime error occurred. The specified LocalDB instance does not exist.)

Po chvilce bojů zjišťuji, že SQL Server 2014 LocalDB má v sobě ?bug?, a to že nezaloží automaticky instanci “v12.0”, ač to o sobě v dokumentaci tvrdí a EntityFramework to očekává: https://connect.microsoft.com/SQLServer/feedback/details/845278/sql-server-2014-express-localdb-does-not-create-automatic-instance-v12-0

Řešení je naštěstí poměrně snadné, z Command Promptu stačí instanci vytvořit:

sqllocaldb create v12.0 12.0

…a hrátky s Entity Frameworkem mohou pokračovat. ;-)

Update: Test/Build Server

Pozor, že na test/build serveru, kde se budou pouštět unit-testy, je potřeba instanci založit pod účtem, který testy pouští. Instance LocalDB nejsou sdíleny mezi účty, není-li explicitně nastaveno jinak (a pozor, že prostý „sqllocaldb share v12.0 v12.0“ ve skutečnosti nasdílí instanci pod názvem „.\v12.0“, nikoliv „v12.0“).

SQL Server ignoruje DEFAULT_SCHEMA pro uživatele v roli sysadmin

Zanechat odpověď

SQL Books online (dnes vlastně TechNet nebo spíš už dokonce MSDN) jsou nekompromisní:

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

Primárně je samozřejmě špatně se na DEFAULT_SCHEMA spoléhat, nicméně při práci na starším projektu mě to překvapilo, když jsem dal uživateli sysadmin roli a najednou mi to přestalo nacházet objekty v DB.

SQL DMV: Most Expensive Queries, Missing Indexes

Zanechat odpověď

Kolem Database Management Views a jejich využití pro SQL performance diagnostics toho napsáno tuny, ale právě tato kvanta různých zdrojů uvádějí spousty různě kvalitních podob dotazu pro Most Expensive Queries.

Nebaví mě pokaždé hledat ten správný, nebo ho dokonce vymýšlet, proto si zde archivuji mojí oblíbenou podobu:

-- Most expensive queries
SELECT TOP 20
	SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
		((CASE qs.statement_end_offset
			WHEN -1 THEN DATALENGTH(qt.TEXT)
			ELSE qs.statement_end_offset
			END - qs.statement_start_offset)/2)+1)
		AS query_text,
    db.name AS [db_name],
    qs.total_elapsed_time/1000 AS total_elapsed_time_ms,
    qs.total_elapsed_time/qs.execution_count/1000 AS average_elapsed_time_ms,
    qs.last_elapsed_time/1000 AS last_elapsed_time_ms,
    qs.execution_count,
    qs.total_worker_time/1000 AS total_worker_time_ms,
    qs.total_worker_time/qs.execution_count/1000 AS average_worker_time_ms,
    qs.last_worker_time/1000 AS last_worker_time_ms,
    qs.last_execution_time,
    qs.total_logical_reads,
	qs.last_logical_reads,
    qs.total_logical_writes,
	qs.last_logical_writes
    --qp.query_plan
    FROM sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
        LEFT JOIN sys.databases db ON (qt.dbid = db.database_id)
    -- WHERE db.name='DatabaseName'
    -- ORDER BY qs.total_logical_reads DESC
    -- ORDER BY qs.total_logical_writes DESC
    -- ORDER BY qs.last_worker_time DESC -- CPU time (active)
    -- ORDER BY qs.last_elapsed_time DESC -- clock time (včetně čekání na locky, atp.)
    -- ORDER BY qs.total_worker_time DESC
	ORDER BY qs.total_elapsed_time DESC

…a rovnou přidávám už méně používaný dotaz pro chybějící indexy (varuji před zkratkovitou úvahou, že tyto indexy je nutné přidat):

SELECT
	mid.statement
	  ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
	  'CREATE INDEX [missing_index_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle)
	  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
	  + ' ON ' + mid.statement
	  + ' (' + ISNULL (mid.equality_columns,'')
		+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
		+ ISNULL (mid.inequality_columns, '')
	  + ')'
	  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
	  migs.*, mid.database_id, mid.[object_id]
	FROM sys.dm_db_missing_index_groups mig
		INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
		INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
	WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) &gt; 10
	ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Databáze zůstane viset ve stavu „Restoring“

Zanechat odpověď

Pokud Vám zůstane databáze z jakéhokoliv důvodu viset ve stavu Restoring, znamená to, že sled předcházejících událostí SQL server interpretoval jako nedokončenou snahu o obnovení databáze. Legitimně se v tomto stavu databáze nachází, pokud chcete např. přehrát nad obnovenou zálohou ještě transaction-logy, atp.

Pokud tento stav pro Vás není žádoucí, nic dalšího už nechcete udělat a potřebujete databázi oživit, potom pomůže

RESTORE DATABASE MyDatabase WITH RECOVERY

…kde MyDatabase pochopitelně nahradíte názvem své DB.

SQL: Jsou dvě data (small)datetime ze stejného dne?

Zanechat odpověď

Potíž je v tom, že typ (small)datetime obsahuje mimo údaje data i čas. Můžeme samozřejmě zařídit, aby naše záznamy měly tento čas 00:00, ale stejně občas na tento problém narazíme.

Měli bychom rozlišit dvě situace, porovnávání s pevným dnem, nebo hledání shod ve variabilních datech.

Hledání shod ve variabilních datech

Jsme zde v situaci, kdy obě porovnávané složky jsou variabilní, u obou se musíme vypořádat s ignorováním složky dne.

SQL Server 2008 a novější verze mají datový typ date. Pokud tedy nejsme zrovna na nějaké pravěké verzi SQL, ukazuje se jako nejrychlejší (ač možná překvapivě) tato konverze:

SELECT MyColumn FROM MyTable
	WHERE CONVERT(date, DateColumn1) = CONVERT(date, DateColumn2)

Pro starší verze SQL serveru je použitelných několik o dost méně výhodných (pomalejších) podob. Protože SQL 2005 a starší již dnes považuji za pravěk, nebudu již rozebírat jejich výhody/nevýhody a rychlost:

...
WHERE
   (YEAR(Datum1) = YEAR(Datum2)
   AND (MONTH(Datum1) = MONTH(Datum2)
   AND (DAY(Datum1) = DAY(Datum2)

WHERE 
   (YEAR(Datum1) = YEAR(Datum2))
   AND (DATEPART(dayofyear, Datum1) = DATEPART(dayofyear, Datum2))

WHERE CONVERT(varchar(10), Datum1, 101) = CONVERT(varchar(10), Datum2, 101)

WHERE FLOOR(CONVERT(float, Datum1)) = FLOOR(CONVERT(float, Datum2))

Porovnání s pevným dnem

Trochu jiná situace nastává v okamžiku, kdy jeden z údajů je pevný. Například hledáme záznamy založené určitého dne

DECLARE @DateFilter smalldatetime
SET @DateFilter = '20090305'

SELECT Created FROM MyTable
	WHERE
		(Created >= @DateFilter)
		AND (Created < DATEADD(day, 1, @DateFilter))

…zde s výhodou celý problém převádíme na porovnávání s konstantami, a pokud máme k dispozici vhodný index, dokonce na operaci Index Seek.

Kupodivu úplně identický execution plan (Index Seek) a se stejnými časy dotazu (testováno na obrovských datech na MS SQL Serveru 2012) dostáváme i pro podobu:

DECLARE @DateFilter date
SET @DateFilter = '20090305'

SELECT Created FROM MyTable
	WHERE CONVERT(date, Created) = @DateFilter

SQL Worst Practices – Slides a dema [TechEd Praha 2013]

Zanechat odpověď

Slides a dema z mé přednášky na konferenci TechEd DevCon Praha 2013:

SQL Worst Practices – TechEd 2013 – Slides (PDF)
SQL Worst Practices – Dema (ZIP)

Videozáznam z přednášky nebyl pořizován.

Performance Tuning with SQL Server Dynamic Management Views – zajímavá PDF kniha zdarma

Zanechat odpověď

…další užitečná kniha zdarma ke stažení od RedGate. Na rozdíl od předchozích 50 Ways to Avoid, Find and Fix ASP.NET Performance Issues je to však hutná kniha o 337 stranách. Je však dobré ji evidovat v patrnosti pro případ řešení specifických situací pomocí DMV.