Sql samples, sql tips

Sunday, April 21, 2013

[SQL 2012] Fehler in Metadaten-Manager in SSAS-Projekt

Fehler in Metadaten-Manager in SSAS-Projekt

Posted: 03 Apr 2013 03:50 AM PDT

Hi allerseits,wir sind seit Ende letzter Woche mit einem hässlichen Fehler konfrontiert und wissen aktuell keinen Ausweg.Wenn man das SSAS-Projekt in Visual Studio 2010 öffnen möchte, kommt folgender Fehler:[quote]Fehler im Metadaten-Manager. Die Dimension mit der ID 'Dim Produktgruppe' und dem Namen 'Dim Produktgruppe', auf die der T04_PlUmsatzSonneBP-Cube verweist, ist nicht vorhanden.Fehler im Metadaten-Manager. Fehler beim Laden von 'T04_PlUmsatzSonneBP' (cube) aus der Datei '\\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data\OLAP.0.db\T04_PlUmsatzSonneBP.8.cub.xml'.[/quote]Auch die Verbindung im Management-Studio wird mit einer Fehlermeldung quittiert.Eine Rücksicherung der Dateien aus dem Ordner "C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data\OLAP.0.db" von vorangegangenen Tagen brachte keinen Erfolg... es kommen immer wieder ähnliche Fehler.Zu diesem Thema konnte ich folgendes in einem Blog finden: [url=http://sqljoe.wordpress.com/2011/03/22/ssas-errors-in-the-metadata-manager-the-dimension-with-id-of-xxx-referenced-by-the-xxx-cube-does-not-exist/]Klick[/url]. Allerdings bezieht sich das auf den SQL-Server 2008 (R2), und nicht auf den 2012er. Generell hätte seine Lösung auch bei uns funktioniert, allerdings haben wir kein "Offline-Projekt", welches wir neu deployen könnten. Der Grund hierfür ist, dass wir aktuell ein neues Reporting- und Planungswerkzeug integrieren, welches es quasi verbietet, offline-SSAS-Projekte aus Visual Studio zu deployen (sonst würden wir die durch diese Software automatisch erzeugten Cubes und sogenannten CheckOut-Dimensionen zerstören).Jetzt stehen wir da, und wissen nicht, wie wir das SSAS-Projekt wieder "zum Leben erwecken" können. Die Datei, die in der Fehlermeldung angemeckert wird, wird aus einem Backup des Verzeichnisses auch automatisch nach Dienststart des Analysis-Service gelöscht (Zusammen mit ca. 1400 weiteren Dateien!!).#############################Hi everybody,since last week, we are faced with an "ugly" error message when opening an SSAS-Project in Visual Studio 2010:[quote]Errors in the metadata manager. The dimension with ID of 'Dim Produktgruppe', referenced by the 'T04_PlUmsatzSonneBP' cube, does not exist.Errors in the metadata manager. An error occurred when loading the T04_PlUmsatzSonneBP cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data\OLAP.0.db\T04_PlUmsatzSonneBP.8.cub.xml'.[/quote]Same or equal error message when trying to connect with Microsoft Management Studio.We restored a backup of the folder, which was a few days older, but this did not solve the problem. The error-message still raises up. There is a blog, where such behaviour was previously mentioned on sql server 2008 (R2) here: [url=http://sqljoe.wordpress.com/2011/03/22/ssas-errors-in-the-metadata-manager-the-dimension-with-id-of-xxx-referenced-by-the-xxx-cube-does-not-exist/]Click[/url]But the given solution does not work in our environment, because we do not have an offline SSAS-Project (offline-development is not allowed because of a new tool for reporting and planning-purpose which creates its own cubes and dimensions out of the tool. So a deployment could lead to deleting their data!)So, what else could we do to restore the SSAS-Project? By the way, about 1400 files are automatically deleted from the mentionend "OLAP.0.db"-folder when starting the Analysis Service after a restore of the corrupted data!?

[SQL Server] Advanced SQL Server 2008 Extended Events with Examples

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[SQL Server] Testing with Profiler Custom Events and Database Snapshots

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[SQL Server] Using REPLACE in an UPDATE statement

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[SQL Server] Handling SQL Server Errors

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[SQL Server] Using Dynamic SQL in Stored Procedures

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[SQL Server 2008 issues] Very interested to know what can be done in the case of a long running select query

Very interested to know what can be done in the case of a long running select query
Huge log backups after Ola's defrag and maint script runs
Help with Triggers
What happens when there is update to rows (in terms of database internals)
How do I get max value based on count?
large object inBuffer cache alert

Very interested to know what can be done in the case of a long running select query

Posted: 20 Apr 2013 09:21 AM PDT

Hi everyone. I'd love to get some help understanding why SQL Server 2008R2 x64 Enterprise Edition does what it does.The SQL Server is dedicated to an application that as I understand it processes workloads which take 5-6 days to complete and then begins processing the next workload.Throughout the week, there are constant inserts/updates/deletes that take place in different SPIDs. During the weekly workload there is a single select query that stays active for almost the entire time. This query does nothing that I can interperate as database activity but holds several locks which prevent me from doing maintenance etc on the SQL Server. The SQL Server has 48 cores and 128GB RAM. We have MDOP set on this instance to 32. Tried MDOP settings of 0, 1, 6, 8, 16, and 32 but have not determined what setting would work best. If I recall, there were a lot of CXPACKET waits when MDOP was set to 0. Parallism threshhold has always stayed at the default value of 5.Subtree cost in the execution plan for this log running select query started at 9000+ but with the addition of a few indexes and even an indexed view, we have reduced the cost to ~90. This seems like a big win but the query still stays open for days with no apparent database activity. The common wait type for the query is ASYNC_NETWORK_IO which lasts for about 2 second on average so I believe that the bottleneck may be the application slowly taking in the data collected innitialy by the long running select query. Does that make sense? I wonder if there is anything we can do on SQL Server's side to improve things. My thought is that since SQL Server is parallelizing the query to 32 threads and then just sitting there for days, it is really not taking advantage of the parallel threads but instead blocking other operations from using them. Could a plan guide that forces MDOP of 1 for this specific query free up the other threads? Mainly, is there anything I can do to prevent the query from staying open for days?Please see the below output collected by Spotlight on SQL Server for the long running spid. Is is only a moment in time but it is pretty typical of what I see all week. Wish I knew how to format it better so the header rows lined up with the output.[code="plain"]SPID Status Blocked By Last Command Memory CPU ms/s I/O per sec Logical Reads Reads Writes Total CPU Total I/O Total Logical Reads Total Reads Total Writes Current Wait Time (ms) Last Wait Type Last Wait Resource Last Batch Time Time Since Last Batch Tran Count Host Process Net Library Login Time93 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:5993 running 0 SELECT 2 0 0 0 0 0 247,635 137,435 1,265,971 137,434 1 1,230 ASYNC_NETWORK_IO 4/18/2013 17:00 1d 21:47:51 0 3672 TCP 4/18/2013 16:59[/code]Thanks for reading and any insight.Howard

Huge log backups after Ola's defrag and maint script runs

Posted: 20 Apr 2013 03:18 AM PDT

I have a SQL 2008 R2 instance that's hosting several OLTP databases for LOB applications. Every night Ola Hallengren's index defrag script runs, followed by a full nightly backup (using Ola's backup scripts). Log backups are taken every hour from 3 am (after the nightly backup concludes) until 11 pm (right before the nightly index defrag script runs).Most backup log files are just a few MB, or even less than a meg. However, the 3 am log backup file often runs between 7 GB and 11 GB because of the index defrag process.My question is whether it would be a good idea to toggle the database recovery model to simple then back to full immediately before the nightly backup runs. The idea would be to get one last good log backup beforehand, then intentionally break the log chain right before the full backup runs since I would never need to restore prior to the latest full backup.What are your thoughts on this idea? Or, should I just live with the large log backup files?Thanks in advance for your advice,Andre Ranieri

Help with Triggers

Posted: 20 Apr 2013 08:12 AM PDT

I have to create 3 triggers for a table - INSERT, DELETE, and UPDATE. The triggers will be used to update the a column named num_rentals in a table named MOVIES each time a customer rental record has been added, deleted or inserted.Thanks.EDIT: completed triggers removedINSERT and DELETE triggers work. I now need to create an UPDATE trigger. See 3rd post.

What happens when there is update to rows (in terms of database internals)

Posted: 20 Apr 2013 07:09 AM PDT

Hi,What happens when there is update to rows (in terms of database internals)?Thanks

How do I get max value based on count?

Posted: 20 Apr 2013 07:08 AM PDT

I have a table that has some result that scan over a period of several days. I want to display a value who's count is the largest over the time range. For example I have the following values in a column.EmpID Name Value1 Tim One1 Tim Two1 Tim Three1 Tim One1 Tim One1 Tim Two1 Tim OneSince One shows up the most, I want to display that value. I would like my output to be EmpId Name Value1 Tim OneThe other scenario is when I have two counts that are the same. For example:EmpID Name Value1 Tim One1 Tim Two1 Tim Three1 Tim One1 Tim Two1 Tim Two1 Tim OneIn this case, I would want to select the maximum of value which would be Two in this case.

large object inBuffer cache alert

Posted: 20 Apr 2013 04:21 AM PDT

HiCan anbody advise on how to handle an alert that tells me i have a large object in the buffer cache. Do i need to clear them out or does sql server flush these out after a certain retention period ? How do i find out what the object is that is causing the issue ?

Sunday, April 21, 2013

Search This Blog