Tag Info

New answers tagged

0

I would try to answer the questions point wise After few days if i check in task manager, it shows the load of 31 GB Memory but actually maximum is alloted to SQL is 28 GB and nothing is running on he server First please note that task manager is not correct place to look for SQL Server memory utilization especially when SQL Server service account has ...


0

So in this case it does seem to have been the 8 tempDB files causing the biggest issue. I ran the analysis suggested here http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/ and found no PAGELATCH issues and a very high proportion of PAGEIOLATCH waits (I don't remember exactly, but ...


2

Although you limited the memory to 28 GB out of 32 GB, this is not the only memory that SQL Server uses. The running programs and needs within Windows take some additional space. If you want to closely tune the memory use, I recommend reading this, which works well for my servers. See Jonathan Kehayias at: ...


0

You could rewrite with a LEFT JOIN: SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName ... -- ### unchanged up to here ##-- , G.TermNo , G.TermNo * G.MaxMonthlyAmount AS TotalAmount -- ### end ### -- FROM ...


0

Since you have identified that the sub-select (SELECT ...) CPH cannot join with higher level nesting, how about changing your sample rewrite as something like: INNER JOIN ( SELECT MAX([iContractID]) AS [iContractID] , MAX([iTermNo]) AS [iTermNo] , MAX([cMonthlyAmount]) AS [cMonthlyAmount] FROM [tbl_ContractPaymentHistory] WHERE ...


0

@Alan Please check once by disabling the audit. That server has an AUDIT on a database. If the audit is enabled, you will get that error. If you disable the audit,query will run fine.


0

Provisioning the SQL Server Agent service account is simply referring to "configuring it". Which you stated you changed the service account from Network Services to Local System, that is provisioning the service to run as "Local System". It is just terminology used in their documentation. Regarding the issue at hand, you need to review the system log within ...


1

You will need to manually merge the existing databases and initialize from that source. One challenge you might face in manually merging the databases is conflicting primary keys. If so, this is typically handled by adding a location-specific identifier column and extending the primary key to include this column. This is known as partitioning your data.


0

Assuming those objects are stored procedures or views (and not tables), you'll need to: script the objects in question drop them from the database (or just remove the schema-binding) change the database collation recreate the objects by running the scripts you created in the first step. As an aside, you didn't need to sp_msforeachtable "ALTER TABLE..." ...


1

The problem was in the query, it seems that it doesn't work in parallel, Here is the parallel version by @usr (Source for query: stackoverflow.com/a/24810980/122718) that do max ALL CPUs to 100%: USE master SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id) INTO #temp FROM sys.objects o1 JOIN ...


0

It depends on when the trigger fires during the transaction. The trigger itself is part of the transactional context you are currently in. This means that any changes you have already made in the transaction so far will be visible to the trigger. To take a simple example, consider this data model: CREATE TABLE Foo (F INT) CREATE TABLE Bar (B INT) INSERT ...


0

Never do shrink database as it increases fragmentation even more. And yes, it looks like sizes are different because of fragmentation, so you have lots of space that stores nothing (half filled pages). This is probably because of incorrect schema design and wrongly chosen clustered indexes. But fragmentation happens on production system and it's good idea to ...


0

Let me answer your question point wise 1.SQL Server express has SQL server agent code built in but disabled. When you go to sp_configure you can see Agent XP enable/disable option this is so because if any point of time you are planning to upgrade SQL Server Express Microsoft upgrade process does not have to do much change but just a minimum to enable SQL ...


1

I would store this as two different INT/BIGINT, one for each side of the decimal point. To make it easier to query, you can also add a computed column that renders the data as a varchar. In other words: CREATE TABLE MyData ( IntegerPart INT NOT NULL , FractionPart INT NULL /* Null of no fraction */ , HumanFormat AS CAST(IntegerPart AS VARCHAR) ...


1

If you want to see pain points of a query capture the actual execution plan via SSMS when you run it. Viewing this within SQLSentry Plan Explorer (free!!) is a bit more easy on the eyes than what SSMS will show. There are some good blog post and articles scattered around the Internet on how to read an execution plan if you need it. Capture Actual Execution ...


0

I suspect it is just timing out if you are using SSMS right click. Need to run the rebuild as TSQL statement. You can right click SSMS and get syntax for drop and rebuild indexes. As for size a fragmented index takes more space. Given the index rebuilds are timing out and you have the large difference in query time you more likely have severely fragmented ...


1

I must say first that using fn_dblog and fn_dump_dblog is unsupported as these commands are not supported by Microsoft. Jonathan Kehayias in his article even pointed out that using fn_dump_dblog multiple times has grave performance implication so use these undocumented commands on your own risk. There is also a way to track page splits, not perfectly though ...


0

After trail and error i managed to use the following sql to find the reason. SELECT OBJECT_NAME(p.object_id), i.name, COUNT(dblog.AllocUnitId) [count] FROM sys.partitions p LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id INNER JOIN fn_dump_dblog ( ...


0

"Throw more hardware at the problem" will not always solve theses kind of issues. What if Paul adds 100Go to his log space, and it still gets filled because of poor programming techniques? Also, adding more space may not be feasible, for example if his Servers are virtualized and the hardware running it is at capacity. Given it is a developper's environment ...


1

For a database in SQL Server, there's a setting called Auto-Close, which is meant to minimize the amount of resources used while SQL Server is running, but no users are actively doing things. This setting is enabled by default in Express Edition instances, as those are usually running on developer workstations where performance isn't critical, and resources ...


0

Looks like the disk subsystem on Server B is performing worse than on Server A but, I used to see disk issues not entirely related to disk specs. You could collect some other performance counters such as physical disk --> avg disk sec write (> 25 ms very slow), memory --> page file usage (> 70% bad), cpu --> processor queue length (> 12 very bad), memory --> ...


8

nvarchar(8) PK NOT NULL ... their PKs return null ... This cannot happen, under any condition. Therefore I must conclude that your 'simplified' repro has simplified things beyond the limit of what you understand. Please post the exact table definition (including all indexes) and the exact query you issue. Null is what comes back from the linq ...


5

If you're using READ UNCOMMITTED isolation level or the WITH (NOLOCK) in your queries, your queries can skip rows or see rows twice. Kendra Little explains it in more detail in her 30-minute webcast There's Something About Nolock.


0

If the following command is run by someone with the authority to do so, the trace will be stopped. exec sp_trace_setstatus @traceid=<trace_id>, @status=0 -- Stop the trace However it still exists in the stopped state. This is the state you are reporting as 'Paused'. If the stopped trace should be ended, then a second command is needed: exec ...


0

The ndf file is an additional file, which you can add to your database in the database properties. You have to think about files and filegroups as well. Otherwise you are not able to control, what is stored in which file. Multiple files can be used mainly to distribute load on your IO system, allow you to implement partial backup strategy for large databases ...


2

NDF is secondary data file, By default it will not be created unless we create the file while creating SQL server database or manually create it after database is created using AlTER DATABASE statement. SQL server by default will create one primary data file and one log file. If it is not visible in sys.master_files means you have not created it. Read more ...


0

You could use a FileSystemWatcher which triggers when the Excel file changes and runs simple code to push the revised file through to SQL Server.


0

Thanks @Joel Brown, your comment pointed me in the direction I needed to go. This is what I ended up with, which satisfies all of my requirements: CREATE TABLE RuleGroups ( ID INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_rulegroups PRIMARY KEY (ID) ) CREATE TABLE Rules ( ID INT NOT NULL IDENTITY(1,1), GroupID INT NOT NULL, ...


2

There are lot of things I would like to say What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model. As far as I know if you change recovery model of database during maintenance window time or when load is relatively less there wont be any problem. It wont create a situation. I'm also ...


0

When you are scheduling your index rebuilds, is this during an maintenance window with no application - user activities ? Line of business might need point-in-time ( PIT ) recovery so leave in FULL and monitor; or get exception for this window. Sounds like you have scoped this change OK ! Best luck.


4

Rather than looking at index utilization, I'd look at the plan cache to find your queries with the highest amounts of logical reads. Usually when I'm dealing with partitioning, I find just a handful of queries that are dominating reads - like 50-80% of the servers' reads overall. Check those queries to see if they're successfully doing partition elimination. ...


1

I will run RESTORE DATABASE MyDatabase WITH RECOVERY to activate it, but then what happens next time the job runs to do log shipping? Will it fail? Should I disable the job manually? Will it overwrite my database and set it back to Restoring... ? Yes they will run. Logshipping is simple copy and movement of logs so when you bring secondary database ...


2

Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time: 1) Backup and Restore Databases - Is this the best option? It's an option. If you need the downtime to be less, you ...


0

You're uses have long running transactions. They need more drive space for the transaction logs. End of story.


3

Since your databases are in simple recovery mode checkpoint will be taken care of by database engine either automatically or when log file grow 70 % of its size, you can also give manual checkpoint . But still a long running transaction can hold log hostage and would not allow it to truncate even if you manually give checkpoint. Log backup would not be ...


2

I would definitely err on the side of keeping the tables in the same database. It makes it easier to maintain referential integrity (unless you hard delete records in which case foreign keys won't work for you anyway) and create consistent backups (simply backup one database and you know it is in a consistent state, instead of taking two+ backups that my be ...


-1

I would do this in the same database because the data belongs together. To create a trigger for this use, it is not practical to insert records to another database. Refer to the following answer on how to design the auditing table. http://stackoverflow.com/questions/7975317/audit-tables-each-field-for-table-or-one-table


0

Hekaton is Microsoft's copy of the VoltDB architecture from Michael Stonebraker. It is a more radical concept than Oracle's In-Memory seems to be. It involves a sharded, shared-nothing architecture where stored prodcedures are isolated and run to completion without any of the buffering and other overhead associated with transaction management - or what ...


2

You can use Excel to save the file as a .CSV (comma-separated-values), and use SQL Server Management Studio to import the file either into an existing table, or potentially a new table. In SQL Server Management Studio, you select the desired target database, right-click the database name, then click 'Import Data', and go through the wizard step-by-step.


0

I am not a expert in Hekaton but as per Kalens article Yes it does but in different way from Oracle. For processing OLTP data, there are two types of specialized engines. The first are main-memory databases. Oracle has TimesTen, IBM has SolidDB and there are many others that primarily target the embedded DB space. The second are applications caches or ...


1

As you found out, using TOP doesn't work here, because that applies to the entire result set, whereas you want the top N rows from each grouping. One way to do this is by using a ranking function; I've used ROW_NUMBER in this example (note: not syntax checked): SELECT a.EmpName, a.Activities, a.Date FROM ( SELECT ...


1

Yes it is possible... I want to call out a few points before offering the solution: You must configure permissions so that the appropriate applications have the full control over the directory and files in the destination. This includes: SQL Server Service Account or whichever account you are running the SQL Server process under. If batch jobs run under ...


0

No there are no specific bad things about using temp tables and temp procedures. For the temp tables you'll want to make sure that you've got indexes as needed when querying from them, but that applies to normal tables as well.


3

You should focus on breaking your ETL process in chunks to manage transaction log growth. There are other options like setting recovery model to bulk log selective transactions but with bulk logged recovery model you loose point in time recovery. And I do agree setting log file on remote storage is not a suggested practice and it will make operations slow.


1

VLF are totally managed internally by SQL server, I am talking about there size. If you have large amount of VLF's for the database its not a good sign.I guess you are referring to large amount of VLF as VLF fragmentation. High amount of VLF can be attributed to fact that autogrowth settings are not proper for database may be very small and database has to ...


1

I doubt that the example you provide is a valid use case for temporary procedures (I don't see any benefit here to using #temp procedures over permanent procedures), but for #temp tables, which have a much wider set of use cases, the only way to fight these arguments with policy-setters is to run the code - using a full load and during typical workload ...


0

Another way, that can be easily extended to more than 3 columns: SELECT a, b, c, Equal = ( SELECT r = CASE WHEN MIN(v) = MAX(v) THEN 'Y' -- all non-null values equal WHEN MIN(v) < MAX(v) THEN 'N' -- at least 2 non equal ELSE 'Y' -- all nulls ...


0

You were almost there with your query. However, your query fails, when validating isnull(A,B) = B, when B is null, since NULL does not equal NULL in sql. try this: declare @t table ( A decimal,B decimal, C decimal ) insert Into @t(A,B,C) values (1,1,1) insert Into @t(A,B) values (2,2) insert Into @t(A,C) values (2,2) insert Into @t(A,C) values ...


0

We had to bring MS in on this one via a paid support call. After a week of dumps, xperf analysis, driver updates, playig with Virtual infrastructure we ruled out all the basics (or so we thought) - over a week later we found the problem - there was over 500,000 sys.server_event_notifications defined - these all looked as follows: name object_id ...


2

I have had great success with overall perfomance gains by moving the clustered index to an identity, and leaving the GUID as the PK. If you have a GUID as a clustered PK, you are telling SQL to physically sort the data randomly, leading to page splits and fragmentation in the main table as well as in the non-clustered indexes. By getting your database on ...



Top 50 recent answers are included