Saturday, September 28, 2013

[how to] Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

[how to] Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

Posted: 28 Sep 2013 07:54 PM PDT

I'm a fan of surrogate keys. There is a risk my findings are confirmation biased.

Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys based on IDENTITY() values.

My background in computer systems tells me performing any comparative operation on an integer will be faster than comparing strings.

This comment made me question my beliefs, so I thought I would create a system to investigate my thesis that integers are faster than strings for use as keys in SQL Server.

Since there is likely to be very little discernible difference in small datasets, I immediately thought of a two table setup where the primary table has 1,000,000 rows and the secondary table has 10 rows for each row in the primary table for a total of 10,000,000 rows in the secondary table. The premise of my test is to create two sets of tables like this, one using natural keys and one using integer keys, and run timing tests on a simple query like:

SELECT *  FROM Table1      INNER JOIN Table2 ON Table1.Key = Table2.Key;

The following is the code I created as a test bed:

USE Master;  IF (SELECT COUNT(database_id) FROM sys.databases d WHERE d.name = 'NaturalKeyTest') = 1  BEGIN      ALTER DATABASE NaturalKeyTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;      DROP DATABASE NaturalKeyTest;  END  GO  CREATE DATABASE NaturalKeyTest       ON (NAME = 'NaturalKeyTest', FILENAME =           'C:\SQLServer\Data\NaturalKeyTest.mdf', SIZE=8GB, FILEGROWTH=1GB)       LOG ON (NAME='NaturalKeyTestLog', FILENAME =           'C:\SQLServer\Logs\NaturalKeyTest.mdf', SIZE=256MB, FILEGROWTH=128MB);  GO  ALTER DATABASE NaturalKeyTest SET RECOVERY SIMPLE;  GO  USE NaturalKeyTest;  GO  CREATE VIEW GetRand  AS       SELECT RAND() AS RandomNumber;  GO  CREATE FUNCTION RandomString  (      @StringLength INT  )  RETURNS NVARCHAR(max)  AS  BEGIN      DECLARE @cnt INT = 0      DECLARE @str NVARCHAR(MAX) = '';      DECLARE @RandomNum FLOAT = 0;      WHILE @cnt < @StringLength      BEGIN          SELECT @RandomNum = RandomNumber          FROM GetRand;          SET @str = @str + CAST(CHAR((@RandomNum * 64.) + 32) AS NVARCHAR(MAX));           SET @cnt = @cnt + 1;      END      RETURN @str;  END;  GO  CREATE TABLE NaturalTable1  (      NaturalTable1Key NVARCHAR(255) NOT NULL           CONSTRAINT PK_NaturalTable1 PRIMARY KEY CLUSTERED       , Table1TestData NVARCHAR(255) NOT NULL   );  CREATE TABLE NaturalTable2  (      NaturalTable2Key NVARCHAR(255) NOT NULL           CONSTRAINT PK_NaturalTable2 PRIMARY KEY CLUSTERED       , NaturalTable1Key NVARCHAR(255) NOT NULL           CONSTRAINT FK_NaturalTable2_NaturalTable1Key           FOREIGN KEY REFERENCES dbo.NaturalTable1 (NaturalTable1Key)           ON DELETE CASCADE ON UPDATE CASCADE      , Table2TestData NVARCHAR(255) NOT NULL    );  GO    /* insert 1,000,000 rows into NaturalTable1 */  INSERT INTO NaturalTable1 (NaturalTable1Key, Table1TestData)       VALUES (dbo.RandomString(25), dbo.RandomString(100));  GO 1000000     /* insert 10,000,000 rows into NaturalTable2 */  INSERT INTO NaturalTable2 (NaturalTable2Key, NaturalTable1Key, Table2TestData)  SELECT dbo.RandomString(25), T1.NaturalTable1Key, dbo.RandomString(100)  FROM NaturalTable1 T1  GO 10     CREATE TABLE IDTable1  (      IDTable1Key INT NOT NULL CONSTRAINT PK_IDTable1       PRIMARY KEY CLUSTERED IDENTITY(1,1)      , Table1TestData NVARCHAR(255) NOT NULL       CONSTRAINT DF_IDTable1_TestData DEFAULT dbo.RandomString(100)  );  CREATE TABLE IDTable2  (      IDTable2Key INT NOT NULL CONSTRAINT PK_IDTable2           PRIMARY KEY CLUSTERED IDENTITY(1,1)      , IDTable1Key INT NOT NULL           CONSTRAINT FK_IDTable2_IDTable1Key FOREIGN KEY           REFERENCES dbo.IDTable1 (IDTable1Key)           ON DELETE CASCADE ON UPDATE CASCADE      , Table2TestData NVARCHAR(255) NOT NULL           CONSTRAINT DF_IDTable2_TestData DEFAULT dbo.RandomString(100)  );  GO  INSERT INTO IDTable1 DEFAULT VALUES;  GO 1000000  INSERT INTO IDTable2 (IDTable1Key)  SELECT T1.IDTable1Key  FROM IDTable1 T1  GO 10

The code above creates a database and 4 tables, and fills the tables with data, ready to test. The test code I ran is:

USE NaturalKeyTest;  GO  DECLARE @loops INT = 0;  DECLARE @MaxLoops INT = 10;  DECLARE @Results TABLE (      FinishedAt DATETIME DEFAULT (GETDATE())      , KeyType NVARCHAR(255)      , ElapsedTime FLOAT  );  WHILE @loops < @MaxLoops  BEGIN      DBCC FREEPROCCACHE;      DBCC FREESESSIONCACHE;      DBCC FREESYSTEMCACHE ('ALL');      DBCC DROPCLEANBUFFERS;      WAITFOR DELAY '00:00:05';      DECLARE @start DATETIME = GETDATE();      DECLARE @end DATETIME;      DECLARE @count INT;      SELECT @count = COUNT(*)       FROM dbo.NaturalTable1 T1          INNER JOIN dbo.NaturalTable2 T2 ON T1.NaturalTable1Key = T2.NaturalTable1Key;      SET @end = GETDATE();      INSERT INTO @Results (KeyType, ElapsedTime)      SELECT 'Natural PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime;        DBCC FREEPROCCACHE;      DBCC FREESESSIONCACHE;      DBCC FREESYSTEMCACHE ('ALL');      DBCC DROPCLEANBUFFERS;      WAITFOR DELAY '00:00:05';      SET @start = GETDATE();      SELECT @count = COUNT(*)       FROM dbo.IDTable1 T1          INNER JOIN dbo.IDTable2 T2 ON T1.IDTable1Key = T2.IDTable1Key;      SET @end = GETDATE();      INSERT INTO @Results (KeyType, ElapsedTime)      SELECT 'IDENTITY() PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime;        SET @loops = @loops + 1;  END  SELECT KeyType, FORMAT(CAST(AVG(ElapsedTime) AS DATETIME), 'HH:mm:ss.fff') AS AvgTime   FROM @Results  GROUP BY KeyType;

These are the results:

enter image description here

Am I doing something wrong here, or are INT keys 3 times faster than 25 character natural keys?

How to flush SQL Server database from RAM to hard disk?

Posted: 28 Sep 2013 04:57 PM PDT

As part of the nightly maintenance plan, my working MS SQL Server 2012 databases are backup-ed with options of simple recovery and full backup. Transaction log is not backup-ed. Also, at nights the MSSQLSERVER service or SQL Server is restarted

Checking the the properties of corresponding files in operating system (Windows Server 2008), I observe that "Modified" time of .mdf files correspond to the time of maintenance plan (making backup) run but .log files modification time is one-two weeks earlier.

Why is such difference and why the operating system fails to update modification time?

So, what is the time and reason of .mdf file modification time?
Is it restart of SQL Server or the time of backup?

How can I estimate when a database was last written to a physical disk, that is how recent is .mdf file of a corresponding database?
How can I force SQL Server to flush the database to hard disk in order to have most actualized .mdf file in case of seeing problems in SQL Server functioning?

How to create properly tree-hierarchy

Posted: 28 Sep 2013 05:01 PM PDT

I'm using web2py to create an application

I've got 2 different roles: client and referents. Every client can have multiple referents. Both client and referent can insert row in a table, called object.

The user table, where client and referent are registered, has a column called "client_id". Every referent in the column_id has the id of the client to whom he reference to.

The problem is: which value should a client have in client_id? A possible answer could be NULL or the id of the client itself.

Which one is better? Or there's something better?

What does this SQLCMD statement do?

Posted: 28 Sep 2013 11:41 AM PDT

I am new to SqlCmd. Can anyone give a short explanation of the following command?

SQLCMD -S VMLIVE-DATA\SQLSERVER -Q   "RESTORE DATABASE GIS_ENG FROM DISK = N'\\10.143.251.101\ClientData\Backup\DatabaseBackups\DB_ENG.bak'   WITH  FILE = 1,   MOVE N'DB_ENG' TO N'\\10.153.201.11\E$\SQLSERVER\Data\DB_ENG.mdf',   MOVE N'DB_ENG_log' TO N'\\10.153.201.11\E$\SQLSERVER\Data\DB_ENG.ldf',    NOUNLOAD,  REPLACE,  STATS = 10"

Snapshot for transactional replication taking too long for large database

Posted: 28 Sep 2013 12:56 PM PDT

I want to establish a transaction replication of a large database (200GB). But when I create the publisher and the snapshot agent starts, it takes very very long time and it sticks on the following message

The process is running and is waiting for a response from the server

enter image description here

but the problem is when I see the size of the snapshot folder, I can see it is growing up! But too slowly.

enter image description here enter image description here

Do you have any Idea about this? or any different way of doing the replication? how can I find the more detailed warning or errors messages?

P.S. In my database there are two tables for Images that they make 98% of the size of my database. when I don't include them in my publication, everything goes fine. but when I Include them, I will face the problem.

Display executed commands and number of executions

Posted: 28 Sep 2013 08:53 AM PDT

How would I go about displaying the SQL statements (say..the first 15 characters) and the number of executions for all that have been executed?

Can the 'returning' clause return source columns that are not inserted?

Posted: 28 Sep 2013 06:24 AM PDT

Here's a minimal example of my real-world problem:

create table t(id serial primary key, rnd double precision);

of course you can return the inserted columns with a returning clause:

with w as (insert into t(rnd) values(random()) returning *)  insert into t(rnd) select random() from w returning *;  /*  | ID |            RND |  |----|----------------|  |  9 | 0.203221440315 |  */

you can also return a literal:

with w as (insert into t(rnd) values(random()) returning *)  insert into t(rnd) select random() from w returning *, 1.0 dummy;  /*  | ID |            RND | DUMMY |  |----|----------------|-------|  | 11 | 0.594980469905 |     1 |  */

but you can't return the source columns:

with w as (insert into t(rnd) values(random()) returning *)  insert into t(rnd) select random() from w returning *, w.rnd;  /*  ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd  */

Is there any way I can get w.rnd out of the final returning clause?

SQLFiddle

Error on changing ownership of database from Files Page in database properties dialog box

Posted: 28 Sep 2013 08:42 AM PDT

I've restored a database and created a new user(same user from which I've logged in) as db_owner. Now when I wanted to see database diagram got an error

Database Diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use files page of Database Properties dialog box or the Alter Authorization statement to set the database owner to a valid login, then add the database support objects

Now when I tried to change ownership from Files page of Database Properties I am getting error as

Set Owner failed for Database 'ABC' . (Microsoft.SqlServer.SMO) Additional Info: an exception occurred in SMO The proposed new database owner James-PC\James is mapped as user James in this database. Parameter name: James-PC\James"

Please help me fix the issue so that I may be able to see database diagrams on this computer after restoring the database backup from another system.

Entity with >150 attributes: One table or many tables with relationships

Posted: 28 Sep 2013 05:44 AM PDT

I have entity with 170 attributes. It's data from vehicle monitoring. Every entity has datatime label and unique id of gps terminal. Datetime and id of terminal - these are conditions for GROUP BY operations. I could create one table for all entity:

CREATE TABLE rows {     terminal_id long reference terminals(id),     time timestamp,     -- description 170 attributes     PRIMARY KEY(terminal_id, time)  }

or I could create many tables with relationships:

CREATE TABLE rows {     row_id long PRIMARY KEY,     terminal_id long  reference terminals(id),     time timestamp -- need create index for group by  }    CREATE TABLE gps {     row_id long references rows(row_id),     -- description gps attributes  }    CREATE TABLE fuel {     row_id long references rows(row_id),     -- description fuel attributes  }  -- etc.

Please advise optimal structure for the database of this type.

How do I write this script?

Posted: 28 Sep 2013 11:43 AM PDT

I have a very large database and I want to select certain records and then set a column value such as:

SELECT name, email   FROM table   WHERE zipcode = 12345   INTO outfile /tmp/file.csv;

Then I want to set the mail_flag equal to today's date such as 

SET mail_flag = 09012013

I'm just not sure how to string this together.

How do I find out how far along my PostgreSQL query is?

Posted: 28 Sep 2013 07:14 AM PDT

I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize).

I understand Postgres won't tell me percentage completeness, is there a way (buried deep in logs, system tables, or otherwise) that I can find out how many rows have been pumped into the destination table or have been read by the SELECT query?

Oracle query is slower the second time

Posted: 28 Sep 2013 11:16 AM PDT

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried lowering it down, but lost the symptom)

The first time I run the query, it's 2 seconds. Subsequent times it's 3, 4, or even 5 seconds. This happens consistently - if I change the spacing, I get 2 seconds again, then it goes back up.

This is the opposite of what I would expect - after the first time, the database doesn't need to parse the query, and blocks should be in read into the buffer cache. The only thing I can think of is that it is writing temp data somewhere, in such a way that it is faster to write and read it than it is to just read it. But that of course makes no sense.

Does anyone know why this is happening? Or any red flags in the execution plan that would cause something like this?

FYI, this is a SELECT, with multiple joins, aggregation, and views. No internal PL/SQL.

Thank you

@Colin It's unlikely another process could be affecting this Dev machine, at least so consistently. Not sure what dynamic sampling is. Same thing as cardinality feedback?

@druzin No bind variables, but no predicate changes either - same hash.

@mustaccio Good idea, although I suspect it's something to do with implicit temp tables or caching. I'll give it a try.

How to measure a perfomance of SQL Server database? [closed]

Posted: 28 Sep 2013 03:36 PM PDT

I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics.

So, I need to measure "performance" of the RDBMS database before performance tuning and optimization and after.

Which metrics are better suited for this?

Trying to answer the obvious questions ahead ...

I/O (hardware) bottlenecks are absent since the SQL Server runs on a virtual rack having plenty of physical resources under it.

The database is used by approx. 60 users (mostly 8 hours a day) with widely varying load (per sec).

This is a company management task, so the results of this task should be easy to grasp.

UPDATE:
the corresponding my question was answered in StackOverflow.com by @Martin von Wittich and, then, deleted by moderator/community:

Run different kinds of queries (INSERT, UPDATE, DELETE) in a loop (e.g. 1000 times), then divide the runtime by the amount of loop iterations. Now you now how long a single query of that type takes.

Then do whatever you want to do to improve the performance, and compare the results.

Quite good for me. Also I do not see why it was deleted in SO with the reason: "This question does not appear to be about programming" and closed in this site with: "There are either too many possible answers, or good answers would be too long for this format."

Access denied when disabling agent job, despite SqlAgentOperator membership

Posted: 28 Sep 2013 12:59 PM PDT

I am attempting to disable a SQL agent job from an account which a member of the SqlAgentOperator role (but not sysadmin)

The doco says this should be possible

3 SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.

...and indeed the code for msdb.dbo.sp_update_job appears to support this - it has explicit handling for exactly this scenario. Yet if I run:

use [MSDB]  go  select suser_sname()  if(is_member('SqlAgentOperatorRole')<>1)      raiserror('Not in required role',16,1)  go  exec dbo.sp_update_job @job_name='a job', @enabled=0

... I get the following error:

Msg 229, Level 14, State 5, Procedure sp_update_job, Line 1 The EXECUTE permission was denied on the object 'sp_update_job', database 'msdb', schema 'dbo'.

The error message appears to indicate the proc's not even being run, but even granting that user explicit EXECUTE on that stored proc doesn't seem to fix it.

Does anyone know how to grant a user the ability to disable/enable SQL agent jobs that they don't own, or has successfully used this functionality. Maybe it's just broken in SQL 2012 SP1 CU4 (which is what I am using)

Minimizing Page Fetches

Posted: 28 Sep 2013 02:24 PM PDT

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use correlated sub-queries to check the state of flags in other tables to determine Eligibility, or am I better of creating Views that utilize Joins that represent the valid Records? As a more concrete example, is this:

Select     Col1, Col2, Col3  From    Table1  Where    (Select RefCol From Table2 Where Table2.PK = Table1.FK) = "Condition"

Superior or Inferior to something like this:

Select    T1.Col1, T1.Col2, T1.Col3  From    T1  Inner Join    T2      On T1.FK = T2.PK  Where    T2.RefCol = "Condition"

-- Edited --

As a corollary question: Is it productive to create Views which contain Intermediary validations? IE, if I repeatedly need to check if T1.Col1 = 1 and T1.Col2 = 0 is it worthwhile to create the following view:

Create View T1Validated As  Select    Col1, Col2  From    T1  Where    Col1 = 1  And    Col2 = 0

And then later use the existence of a record in T1Validated in subsequent checks, or is that likely to produce additional database page retrievals and/or table scans?

Query to get reposts from people the user is following

Posted: 28 Sep 2013 01:24 PM PDT

I have a table posts where all the posts by users are stored, the structure of this table is as follows

| post_id | post_user | post_content | post_date |

the users table is as follows

| user_id | username | user_joined |

user relationship table is as follows

| follower | following | rel_date |

this is the query I am using to get the posts from people that user is following to show them.

SELECT * FROM posts WHERE post_user in(SELECT follower from user_follow where following=$loggedin_user)

Now I want users to share posts, for which I created a table repost_user as follows

| repost_user | original_post_user | post_id | repost_date |

I want to get posts from people that user following, which includes reposts too.. How do I do this?

EDIT : How my resultset should look

post_id | post_content | post_user | post_date | is_repost | repost_user | repost_date

for eg if its normal post the row should look like

23 | <some content> | 3 | <post date> | false | null | null |

if its a repost the row would be

23 | <some content> | 3 | <post date> | true | 2 | <repost_date> |

Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop

Posted: 28 Sep 2013 07:24 PM PDT

I need the Oracle 11g Release 2 sample schemas (HR, OE, etc.) in order to do most of the available online tutorials. I was hoping to install Oracle Express Edition on my Windows 7 laptop to get these; but I have never heard of anybody successfully installing Oracle XE on a 64-bit Windows platform.

Is there a version of Oracle XE 11g R2 available for Windows 7? And if so, could you please point me to it?

Thanks...

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

Posted: 28 Sep 2013 06:24 PM PDT

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour.

I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has 1000 columns mostly boolean and a few varchar.

Currently the fastest way i've found to get the information into my DB until now was:

Process raw data into CSV files. Load Data In File to rawData Table. Insert rawData table into Table1. (on dupe key do my function) Truncate rawData Repeat. Worked fine until im merging 6M+ Rows into 100M rows and expecting it to take under an hour.

I got 16G of ram so I set my Key_Buffer_Pool to 6G. I have my query cache pool to 16M I have my query cache limit to 10M I would just replace the information however it has to be an Upsert, Update the fields that are true if exists and insert if it does not.

Things im looking into atm; - Possibly switching server table to InnoDB? |-> Not sure about the performance, as the insert into an empty table is fine, its the merge that's slow.

Maybe allowing more table cache? Or even Query Cache? mysql sql mysqli innodb myisam

Merge Code:

b.3_InMarket = (b.3_InMarket OR r.3_InMarket),

To compare my 2 bool columns.

Update

  • Ok I set Raid0
  • Changed my query to Lock Write on tables when inserting
  • When importing csv im disabling keys then re-enabling them before upsert.
  • Changed concurrent_insert to 2

USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view)

Posted: 28 Sep 2013 12:24 PM PDT

Does anyone know what triggers the USER_ID field in the log.xml to be populated? The value also exists in the V$DIAG_ALERT_EXT view.

I've found by observing the logs that if a temp tablespace fills up, it will log the USER_ID of the problematic SQL statement causing the issue. But other than that, it appears that value is always NULL.

How can I generate a usage log?

Posted: 28 Sep 2013 05:48 PM PDT

I'm trying to automatically generate an events log report in SQL Server that records events including successful login, Create/Delete Account, An Account was Unlocked, Account locked due to maximum login attempts being reached. This query will also be customized for After Hours Login.

I already know that some of data requested can be generated by using SQL Profiler or C2 audit, Event logs and the sys.login_token, sp_who, etc.

However, I cannot get to the SQL script that would allow me to run the reports at any given time.

Bitmask Flags with Lookup Tables Clarification

Posted: 28 Sep 2013 08:24 PM PDT

I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries based on these fields using AND or OR logic.

Using a calculated field, where I just convert the bits into an integer value, I can easily find rows that match an AND query, by using a simple WHERE rowvalue = requestvalue, but the OR logic would require using bitwise & in order to find matching records.

Given that I would need to work with several of these columns and select from hundreds of millions of records, I feel that there would be a huge performance hit when doing bitwise & operations to filter my SELECT results.

I came across this answer from searching and it looked like it may fit my needs, but I need some clarification on how it is implemented.

Is this as simple as creating a lookup table that has all possible search conditions?

Example for 3 bits using (a & b) (Edit: Wrong bitwise op)

001,001  001,011  001,101  001,111  010,010  010,011  010,110  011,011  011,111  etc

The author mentions that it's counter-intuitive initially, but I can't help but feel I'm interpreting the solution incorrectly, as this would give me a single lookup table with likely billions of rows.

Any clarifications on the answer I linked above or other suggestions that would preserve the existing database are appreciated.

Edit: A more concrete example using small data.

Four flags, HasHouse,HasCar,HasCat,HasDog, 0000 is has none, 1111 is has all.

Any number of flags, from all to none, can be flipped, and results must be filtered where selection matches all (Using exact value comparison) or at least 1 (Using bitwise &).

Adding a single calculated column for each bitmask is ok, but adding a column for each bit for more than 100 bits, coupled with how to insert/update the data is why I'm trying to find alternative solutions.

SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?

Posted: 28 Sep 2013 03:24 PM PDT

I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows written from the catalog tables, and it would be greatly beneficial to be able to tie them together with the calling job.

SQLite writing a query where you select only rows nearest to the hour

Posted: 28 Sep 2013 05:24 PM PDT

I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a unix timestamp. There is no regularity to the timestamp (i.e. the zero minute of the hour may not contain a reading, 00:59:55 and the next measurement could be 01:01:01) and days may be missing.

What I need is the row nearest to the hour, with the timestep rounding to the hour, as long as the nearest value is not more than 30 minutes away from the hour.

Where a matching hour could not be found it would be helpful if the query could include a time but no value.

I realise I'm asking a lot, but this would be incredibly helpful Thanks for taking the time to read this. James

BTW, The table is just PK (autoincrement),timestamp,value, sensor id(FK). I've tried this to get the data out: 

SELECT strftime('%S',time, 'unixepoch'),strftime('%M',time, 'unixepoch'),strftime('%H',time, 'unixepoch'), strftime('%d',time, 'unixepoch'), strftime('%m',time, 'unixepoch'), strftime('%Y',time, 'unixepoch'), value from Timestream where idSensor=359;

Mongo replication lag slowly increasing

Posted: 28 Sep 2013 09:24 AM PDT

I am running a replica-set in production with slaveOk = false using mongo 2.0.7 in AWS The replication lag on one of the servers is close to 58 hours. This replication lag is sometimes decreasing ( at very slow rate ) and sometimes increasing, but overall its lag is increasing 1-2 hours per day.

  • I restarted the server, but seeing no benefit
  • The read rate is 10 times of the read rate, compared to the other secondaries.
  • I checked the logs, but nothing weird
  • The lock % is very high ( close to 100% ) only on that server( and there is no such issues with the capped collection ). Other secondaries hardly have 10-20 lock%

One more weird thing that I noted about this server ( secondary ) is that mms is showing the version to be 2.2.1 and type as 'standalone' server, which is not the case ( cross checked using db.version() and rs.status() command )

Unable to start Oracle Database Server : Get an error as I try to start it

Posted: 28 Sep 2013 10:24 AM PDT

I just installed Oracle Database Express Edition 11g Release 2 for windows.It created a short cut icon on the desktop :

enter image description here

but as I click this icon I see this dialog box :

enter image description here

What is it ? How do I start my Oracle Database Server ?

SQL Server replication subscriptions marked as inactive

Posted: 28 Sep 2013 11:24 AM PDT

Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever?

It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription every time.

Note, I'm not talking about the subscriptions being marked as expired...just as inactive.

Thank you.

What's the difference between a temp table and table variable in SQL Server?

Posted: 28 Sep 2013 09:18 AM PDT

This seems to be an area with quite a few myths and conflicting views.

So what is the difference between a table variable and a local temporary table in SQL Server?

Is nested view a good database design?

Posted: 28 Sep 2013 10:51 AM PDT

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect statement.

Students

SELECT studentID, first_name, last_name, SchoolID, ... FROM students    CREATE VIEW vw_eligible_student  AS   SELECT * FROM students  WHERE enroll_this_year = 1

Teachers

SELECT TeacherID, first_name, last_name, SchoolID, ... FROM teachers    CREATE VIEW vw_eligible_teacher  AS   SELECT * FROM teachers  WHERE HasCert = 1 AND enroll_this_year = 1

Schools

CREATE VIEW vw_eligible_school  AS   SELECT TOP 100 PERCENT SchoolID, school_name     FROM schools sh   JOIN       vw_eligible_student s        ON s.SchoolID = sh.SchoolID  JOIN        vw_eligible_teacher t       ON s.SchoolID = t.SchoolID

At my workplace, I have investigated one of our in-house database application. I checked through the objects found out that there are two or three layers of the view stack each other. So that was remind me about what I read in the past. Can any one help explaining it?

If it is not OK to do so, I want to know that it is limited to just SQL Server or it is for database design in general.

Additional Info: I updated an example from my company. I change a bit to be more general without too many technical (too many columns in this example). Mostly the nested view we used is based on abstract or aggregated view. For example, we have a large student table with hundred of columns. Say, Eligible Student View is based on students who enrolls this year. And student eligible view could be use other places such as in stored-procedure.

Posted by at No comments:

[MS SQL Server] CACHESTORE_SQLCP flush automatically?

[MS SQL Server] CACHESTORE_SQLCP flush automatically?

CACHESTORE_SQLCP flush automatically?

Posted: 27 Sep 2013 11:38 PM PDT

Hi.I have checked my production database for top memory consumer components Database enabled - AdHoc workloads and force parameterzation (for the reason too much single plans created and wasted memory)1. CACHESTORE_SQLCP - using Constantly 2.5GB due to database do not have SP and all.. Application request Ad Hoc query only..2. TokenAndPermUserStore - using 800 MBI cleared the TokenAndPermUserStore by using command (dbcc freesystemcache('TokenAndPermUserStore') and automatically reduce the CACHESTORE_SQLCP memory usage, what could be reason? Thanksananda

Log not available error 9001

Posted: 19 Dec 2010 11:31 PM PST

Came in to work this morning to face a bunch of alerts for severity 21 errors."DESCRIPTION: The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database."Hmm.. The drive the log is on was available and logs for other DB were on it. Plenty of space left. Window Application event log showed no errors other than the one listed above. Windows System log showed no errors. I ran dbcc checkdb on the database and the only error reported was that the log was not available.I took the database offline, then brought it online again and all seems good now. DBCC Checkdb gives no errors. DBCC loginfo(0) gives info, so I'm assuming the log is available. Now just trying to figure out what happened.Took a closer look at the SQL error log and I see this:12/20/2010 02:30:01,spid20s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:30:01,spid20s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:20:29,spid18s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:20:29,spid18s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:00:14,spid217,Unknown,Recovery completed for database NetPerfMon (database ID 18) in 6 second(s) (analysis 1 ms<c/> redo 1212 ms<c/> undo 4437 ms.) This is an informational message only. No user action is required.12/20/2010 02:00:13,spid217,Unknown,1 transactions rolled back in database 'NetPerfMon' (18). This is an informational message only. No user action is required.12/20/2010 02:00:09,spid217,Unknown,21 transactions rolled forward in database 'NetPerfMon' (18). This is an informational message only. No user action is required.12/20/2010 02:00:03,spid19s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:00:03,spid19s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:00:03,spid212,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.12/20/2010 02:00:03,spid212,Unknown,Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.12/20/2010 02:00:03,spid15s,Unknown,Transaction (Process ID 15) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.12/20/2010 02:00:03,spid15s,Unknown,Error: 1205<c/> Severity: 13<c/> State: 51.12/20/2010 02:00:03,spid15s,Unknown,Transaction (Process ID 15) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.12/20/2010 02:00:03,spid15s,Unknown,Error: 1205<c/> Severity: 13<c/> State: 51.12/20/2010 02:00:02,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 02:00:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 01:30:01,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 01:30:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 01:00:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 01:00:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 00:30:01,spid206,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 00:30:01,spid206,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 00:00:23,spid18s,Unknown,This instance of SQL Server has been using a process ID of 1788 since 12/7/2010 9:47:54 PM (local) 12/8/2010 3:47:54 AM (UTC). This is an informational message only; no user action is required.12/20/2010 00:00:01,spid295,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 00:00:01,spid295,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 23:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 23:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 23:00:01,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 23:00:01,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 22:30:02,spid210,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 22:30:01,spid210,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 22:00:01,spid223,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 22:00:01,spid223,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 21:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 21:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 21:00:01,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 21:00:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 20:30:02,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 20:30:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 20:00:01,spid222,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 20:00:01,spid222,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 19:30:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 19:30:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 19:00:03,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 19:00:02,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 18:30:01,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 18:30:01,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 18:00:01,spid218,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 18:00:01,spid218,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 17:30:01,spid196,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 17:30:01,spid196,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 17:00:02,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 17:00:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 16:30:01,spid214,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 16:30:01,spid214,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 16:00:01,spid229,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 16:00:01,spid229,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 15:30:02,spid205,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 15:30:02,spid205,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 15:00:02,spid219,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 15:00:01,spid219,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 14:30:01,spid213,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 14:30:01,spid213,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 14:00:01,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 14:00:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 13:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 13:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 13:00:02,spid223,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 13:00:01,spid223,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 12:30:01,spid222,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 12:30:01,spid222,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 12:00:02,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 12:00:01,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 11:30:01,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 11:30:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 11:00:01,spid220,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 11:00:01,spid220,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 10:30:01,spid206,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 10:30:01,spid206,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 10:00:02,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 10:00:02,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 09:30:02,spid214,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 09:30:01,spid214,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 09:00:03,spid225,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 09:00:01,spid225,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 08:30:01,spid199,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 08:30:01,spid199,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 08:00:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 08:00:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 07:30:02,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 07:30:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 07:00:01,spid216,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 07:00:01,spid216,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 06:30:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 06:30:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 06:00:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 06:00:00,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 05:30:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 05:30:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 05:00:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 05:00:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:30:01,spid207,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:30:01,spid207,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:00:04,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:00:04,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:00:00,spid207,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:00:00,spid207,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 03:30:01,spid198,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 03:30:01,spid198,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 03:00:05,spid234,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 03:00:04,spid234,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 02:52:32,spid78,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 02:52:32,spid78,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 02:52:29,spid71,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 02:52:29,spid71,Unknown,Starting up database 'SpotlightManagementFramework'.2 AM is when my maintenance jobs run. It looks like, for some reason, checkdb got stuck in a loop on this database and had tons of spids working. The first "log uinavailable" error occurs at 12/20/10 2:00:03 with a state value of 5. The subsequent ones all have a state value of 1. I haven't been able to find out what the state value represents. I also noticed that the DBCC CHECKDB messages are appearing at times when DBCC is not scheduled to be run - at roughly half hour intervals through out the day. My transaction log backups run then, but I have verified that job does not perform dbcc.It looks like the clusters of checkdb messages started happening on 12/7/10 at 10 PM, which is just after I restarted the server after applying SQL 2008 SP2.Anyone have any ideas?

Calculating Memory usage

Posted: 27 Sep 2013 03:15 PM PDT

Hi, I wanted to calculate the Total SQL Server Memory usage using DMVs.From sql server 2008, I have below direct query. What I am trying is to get the same value using sys.dm_os_memory_clerks. For memory troubleshooting we normally use DBCC MEMORYSTATUS and sys.dm_os_memory_clerks.I am personally comfortable with tsql and wanted to use sys.dm_os_memory_clerks.Infact, I use sys.dm_os_memory_clerks to see where sql server memory has gone or in other wordswhich component is taking up more memory within sql server. But here, i am interested in only calculating total sql server memory usage which i am not able to get using below query. Notes: I am working on SQL 2008 Developer Edition , 10.0.5500 x64, OS 64bit i.e. win 7 sp1 and LPM is enabled as service account is Local System.-- sql 2008 direct query to find out sql server overall memory usage select virtual_address_space_committed_kb/(1024) as [Total Overall SQL Server Memory usage(MB)]--RAM+pagefile. Does this include AWE and MTL memory as well when we say commit;from sys.dm_os_process_memorygo--output --249 mb-- Reference Link:http://blogs.msdn.com/b/sqljourney/archive/2013/03/16/an-in-depth-look-at-sql-server-memory-part-3.aspxI am trying get the same value using sys.dm_os_memory_clerks. Why am I not getting the same value. Am I missing anything to sum up? Basically, I wanted to use this query for sql 2005 instances. select SUM(single_pages_kb+multi_pages_kb+awe_allocated_kb+shared_memory_committed_kb+virtual_memory_committed_kb)/1024 from sys.dm_os_memory_clerks --output --144 mb I am getting 144 as my output, above sql 2008 query returns 249 value. Why there is a difference? I calculated using perfmon counters i.e Process:Private Bytes(Sqlservr) + SQL Buffer Manager: Total Pages which I am getting value close to 245 which is acceptable.Reference Link :http://blogs.msdn.com/b/joesack/archive/2009/01/08/find-non-buffer-pool-memory-memtoleave-in-private-bytes.aspxBut why using sys.dm_os_memory_clerks I am getting a low value ?Please help.Thank you.
Posted by at No comments:

[SQL 2012] Lazy Spool - What is causing it ?

[SQL 2012] Lazy Spool - What is causing it ?

Lazy Spool - What is causing it ?

Posted: 27 Sep 2013 04:07 AM PDT

I removed the TOP, tried commenting out the WHERE, OR clauses, but cant seem to lose the Lazy Spool !Thanks for any help!USE [JobPortal9_10_13]GO/****** Object: StoredProcedure [dbo].[GetAllJobsSearchedDynamicQueryNew_Vikas_5_21_13] Script Date: 9/27/2013 11:56:57 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ============================================= -- -- Create date: <Create Date,5-7-13,> -- Description: <Description,[Get All Jobs Searched Structured SQL],> -- -- ============================================= ALTER Procedure [dbo].[GetAllJobsSearchedDynamicQueryNew_Vikas_5_21_13] -- Add the parameters for the stored procedure here @Title varchar(250), @CompanyID INT = NULL, @Industry int, @Industry2 int, @Industry3 int, @Date int, @JobTitle int, @JobType int, @Experience int, @Education int, @State int, @City int, @Salary int, @MaxSalary int, @fromRec int, @toRec int, @SortType VARCHAR(50),@SortOrder VARCHAR(10) AS IF @CompanyID < 1 SET @CompanyID = NULLDECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));DECLARE @C TABLE( ID int, CityID INT, StateID INT, Location VARCHAR(50))DECLARE @HasLocation BIT = 0IF (@CITY IS NOT NULL AND @CITY > 0) OR (@State IS NOT NULL AND @State > 0)BEGIN SET @HasLocation = 1 IF @City < 1 SELECT @City = NULL IF @State < 1 SELECT @State = NULL INSERT INTO @C SELECT ID, CityId, RegionId, ((SELECT TOP 1 NAME FROM Cities C WHERE C.ID = CityId) + ', ' + (SELECT TOP 1 NAME FROM Regions R WHERE R.ID = RegionID)) LOCATION FROM Companys WHERE (@City IS NULL OR CITYID = @City) AND (@State IS NULL OR REGIONID = @State);END;WITH CTE1 (RowID,id,CompanyID,title,contactperson,lastmodified,description, workexperience,jobtypeid,AcademicExperienceTypeId,workexperiencetypeid, industryid,industryid2,industryid3,salaryminid,salarymaxid, jobTitle, --city,state, --PostalCode, --name, positions,deadline)AS( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowID, id, CompanyID, title, ContactPerson, LastModified, description, isnull((select we.[Name] from workexperiencetypes we where we.id=workexperiencetypeid),'') as workexperience, jobtypeid,AcademicExperienceTypeId,workexperiencetypeid, industryid,industryid2,industryid3,salaryminid,salarymaxid, isnull((select jt.[Name] from jobTitles jt where jt.id=jobtypeid),'') as jobTitle, positions,deadline FROM EmploymentOpportunities WHERE (@Title IS NULL or title = @Title) and (@Industry = 0 OR industryid = @Industry) and (@Industry2 = 0 OR Industryid2 = @Industry2) and (@Industry3 = 0 OR Industryid3 = @Industry3) and (@Date = 0 OR lastmodified >= @Date) and lastmodified is not null and lastmodified > @ActualDate AND (@CompanyID IS NULL OR COMPANYID = @CompanyID) and (@JobTitle = 0 OR title = @JobTitle) and (@JobType = 0 OR jobtypeid = @JobType) and (@Experience = 0 OR workexperiencetypeid = @Experience) and (@Education = 0 OR academicexperiencetypeid = @Education) --and (@State = 0 OR c.RegionId = @State) --and (@City = 0 OR c.CityId = @City) and (@Salary = 0 OR SalaryMinID >= @Salary) and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary) AND (@HasLocation = 0 OR COMPANYID IN (SELECT ID FROM @C)))SELECT *, (SELECT MAX(RowID) FROM CTE1) TotalCount, isnull((select we.[Name] from workexperiencetypes we where we.id=workexperiencetypeid),'') as workexperience, isnull((select jot.[Name] from jobtypes jot where jot.id=jobtypeid),'') as jobtype, isnull((select edu.[Name] from Degree edu where edu.Id=AcademicExperienceTypeId),'') as education, isnull((select ind.[Name] from industries ind where ind.id=industryid),'') as industryname, isnull((select ind.[Name] from industries ind where ind.id=industryid2),'') as industryname2, isnull((select ind.[Name] from industries ind where ind.id=industryid3),'') as industryname3, isnull((select jt.[Name] from jobTitles jt where jt.id=jobtypeid),'') as jobTitle, City + ', ' + [State] + ', ' + PostalCode AS LocationFROM (SELECT ROW_NUMBER() OVER (ORDER BY --id) RowNumber, CASE WHEN @SortOrder = 'ASC' THEN CASE WHEN @SortType = 'LastModified' THEN CAST(LastModified AS VARCHAR(50)) WHEN @SortType = 'Location' THEN City WHEN @SortType = 'Title' THEN Title END END ASC, CASE WHEN @SortOrder = 'DESC' THEN CASE WHEN @SortType = 'LastModified' THEN cast(LastModified AS VARCHAR(50)) WHEN @SortType = 'Location' THEN City WHEN @SortType = 'Title' THEN Title END END DESC, id) RowNumber, * FROM (SELECT *,(SELECT TOP 1 NAME FROM Cities CT WHERE ID = (SELECT TOP 1 CO.CityId FROM COMPANYS CO WHERE CO.ID = CTE1.CompanyID)) City, (SELECT TOP 1 AbbreviatedName FROM Regions CT WHERE ID = (SELECT TOP 1 CO.RegionId FROM COMPANYS CO WHERE CO.ID = CTE1.CompanyID)) [State], (select top 1 PostalCode from companys co where co.id = cte1.CompanyID) PostalCode FROM CTE1) as p0)AS P1where RowNumber BETWEEN @fromRec AND @toRec OPTION(Maxdop 8)

Saving maintenance plans from prior versions using SSMS 2012 changes precedence constraint

Posted: 27 Sep 2013 09:19 AM PDT

I ran into strange behavior with SSMS 2012. Here's a test I did to verify it's a problem. I created a basic maintenance plan using SSMS 2005. I added a backup database task to backup the model database (as an example) to a folder that does not exist. I then added an execute t-sql statement task to send an email to me. I connected the backup task to the email task using the precedence constraint and set it to "failure" as well as a "logical or". I saved it and ran it to verify it will send me an email on failure. It did. I closed it.Next I opened up the maintenance plan using SSMS 2012 and set the reporting and logging to generate a file. I then saved the plan and closed it. I opened it again in SSMS 2005 to find the precedence constraint is now a "logical and". In SSMS 2012, it still shows as a "logical or". The reason I need the "or" is because my actual maintenance plans contain several tasks. They should be set to email us when any of the tasks fail, not all of them. When it's set to "and", we don't get an email when one of the tasks fails.Any ideas why this is happening? I have now learned (the hard way) to not edit or save maintenance plans in 2012 that were created in prior versions. I had the same problems with plans originally created in SQL 2000, 2008, and 2008 R2. Thanks!
Posted by at No comments:

[T-SQL] compare rows

[T-SQL] compare rows

compare rows

Posted: 27 Sep 2013 11:34 PM PDT

Hi,Please help me to find out conflicts between two records.Scenario: as mentioned below i need to add flag conflict 1 or 0.problem : find studentid's having course more than 1 on a single date and if StartTime of second rows comes between Starttime and Endtime of first row then update conflict flag as 1. I want to compare all rows and find out conflicts. Plase help me how to aschieve this?StudentID Course StartDate StartTime EndTime Conflict1001 Chem 11/4/2013 0:00 7:00 AM 6:00 PM 11001 Phy 11/4/2013 0:00 11:00 AM 2:00 PM 11001 Math 11/4/2013 0:00 8:00 PM 11:00 PM 01001 Bio 11/4/2013 0:00 4:00 AM 7:00 AM 1Thanks,

find date differnce from AM PM format

Posted: 27 Sep 2013 06:31 PM PDT

Hi,I want to find difference between two times which is in AM PM format. Please suggest any idea how to do that?ThanksAbhas

Find difference in time which is AM PM format

Posted: 27 Sep 2013 06:33 PM PDT

Hi,I want to find difference between two times which is in AM PM format. Please suggest any idea how to do that?ThanksAbhas

Is possible create numbered sequences

Posted: 06 Aug 2013 03:18 AM PDT

ROW_ID12233344445555566666677777778888888899999999910101010101010101010111212131313Up to 10000Is possible create numbered sequences like the ROW_ID above, where the row numbers repeat themselves up to 10000 into an empty column? In SQL serverThanks

Error converting data type nvarchar to numeric

Posted: 27 Sep 2013 02:33 AM PDT

Maybe I am a bit dense today since it is a Friday and I am looking towards the weekend.. But this isn't making sense to me.I have a web form with a QTY in it, it has javascript to only allow numbers, but people disable their javascript and enter crazy things in qty fields sometimes..SO... I am writing some code and getting the error. I have simplified it down to this, but still get the error.Doesn't make sense to me since I am using the ISNUMERIC to only apply the CAST function to records that are actually numbers.select case when ISNUMERIC(QtyOrdered)=1 then CAST(QtyOrdered AS decimal(19,6)) else 0.0 end as Qtyfrom ordersAny ideas? Thanks in advance.
Posted by at No comments:

[SQL Server 2008 issues] Need to migrate from oracle to sql server

[SQL Server 2008 issues] Need to migrate from oracle to sql server

Need to migrate from oracle to sql server

Posted: 27 Sep 2013 03:10 AM PDT

HI All,We have requirement to migrate a database from oracle to sql server 2008 .What is best method to do the migration?We are Planing to use SSMA tool for migration.Can any one tell me what are basic pre-requests and steps to follow?THanks in advance............

switch operation in data partitioning

Posted: 27 Sep 2013 12:13 AM PDT

How switch operation works in data partitioning internally.is it logical or physical operation.can it be done online.

Prevent backups on C:\ drive?

Posted: 26 Sep 2013 11:39 PM PDT

Can anyone - off the top of their heads - think of a way of preventing backups being taken to the C:\ drive?I've run a very quick google and didn't spot anything obvious. Nothing foolproof that doesn't risk interfering with other operations strikes me. This may be a slight case of FridayAfternoonHead which will leave me with egg on my face for asking, but I'll take that If you've got something, you can point and laugh, it's OK. :-)

Empty disk - what to do? (performance)

Posted: 27 Sep 2013 04:49 AM PDT

Hi guys,So a server has been performing badly for the last month+ & last night I created some missing indexes which improved the performance a lot. However I noticed that it had 2*RAID5 disks (both 10k rpm i think...they're 10k something) with the following split:RAID5 disk #1: software, pagefile, .mdfs, .ldfsRAID5 disk #2: nothingI guess it was added a while ago for the database ("Data" in the disk virtual name) but nothing got moved. So I was looking for opinions of what you'd suggest moving? I guess it's simply a matter of .mdf or .ldf? Or you'd move tempdb across too with the .ldfs?The performance boost of yesterday has pretty much given me free-reign on the server to experiment so other things I was planning to do:1. increase the cost of parallelism threshold from the default (cxpacket is the 2nd highest stat after the index change, after some wait type similar to IO_NETWORK).2. Initialise IFI3. Add extra .mdfs for tempdp (currently 1*4gb mdf)4. possibly add a couple more indexes depending on the results of my trace earlier today.I really want to see what effect the isolation level would have on how the wait types vary too although I'm not sure if they'd give the go ahead for that test (won't be this weekend at least) since I'd probably need to restore from backup if it proved ineffective...although for a largely OLAP system it should be better?The db is 2005 SEThanks for any feedback on my ideas :o // testing in prod since 2013

Conditional Formatting in SSRS Based on a Range of Values

Posted: 20 Sep 2013 08:07 AM PDT

I am building a backup status report in SSRS 2008. Here is a screenshot of what I have so far:[img]http://skreebydba.files.wordpress.com/2013/09/backupstatus.png[/img]I want to change the font color of the Backup Status to red if the status is FAILED using this conditional logic:=IIF(Fields!backupstatus.Value <> "SUCCESS", "Red", "Black")What I want to do now is change the font color of the Instance Group value to red if any of the Backup Status values in that group are FAILED. So in the screenshot above, DEVSQL08 should be displaying in red as well in the left-hand column.Any assistance would be appreciated.Thanks,Frankblog [url=http://skreebydba.com]skreebydba.com[/url]twitter [url=https://twitter.com/skreebydba]@skreebydba[/url]

Need to redirect .net app to another sql server

Posted: 27 Sep 2013 01:56 AM PDT

Hi,I'm dealing with a situation where one of our developers is compiling a component with the connection string to a SQL Server instance included in the compile. Is there a way to redirect the connection from the compiled connection to another SQL Server instance. Connection parameters including database and security remain the same. Basically we're looking for a situation where the compiled component can retain the same connection information, but have it actually connect to ServerB rather than ServerA.

convert string to date SSIS 2008 R2

Posted: 19 Sep 2013 03:41 AM PDT

hi all,I never found the SSIS 2008 R2 section so I am posting this here since it really is of somewhat general interest:how do I convert a date in string format into a string that can be converted to a date.If my string were YYYY-MM-DD HH:MM:SS.000 the conversion goes well.I know how to convert YYYYMMDD into the format above, however my date strings are M/D/YYYY and they are not even padded.Do you know how to parse M/D/YYYY and get YYYY-MM-DD HH:MM:SS.000 with the syntax of SSIS?I tried ISNULL(DATE) ? NULL(DT_DBDATE) : (LEN(DATE) == 0 ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,2),4) + "-" + SUBSTRING(TRIM(DATE),1,FINDSTRING("/",DATE,1) - 1) + "-" + SUBSTRING(TRIM(DATE),FINDSTRING("/",DATE,1),FINDSTRING("/",DATE,2) - 1)))but there's something wrong and I can't find the mistake,Thanks,kowlasky

Time out occurred while waiting for buffer latch -- type 2. Need Help.

Posted: 10 Apr 2012 07:38 PM PDT

Hello,I hope somebody can help me, I am getting the followng error on my SQL Server 2008 R2 64bit machine.Time out occurred while waiting for buffer latch -- type 2, bp 00000007BEFFE000, page 1:1396234, stat 0x4c00309, database id: 7, allocation unit id: 72057594080591872, task 0x0000000007A31828 : 1, waittime 300, flags 0x1a, owning task 0x0000000004231AC8. Continuing to wait.This error follows a stack dump, I believe the transaction logs were full at the time this happened. If I look at sp_who2, I notice that there is a lot of Suspended DELETE commands for the database , there are about 20 under the same SPID number as well as 5 others under another SPID number. Should I kill these processes?I have tried running a DBCC CheckDB for this database but it fails, saying it could not get exclusive access and failed to take a snapshot.Not sure what to do here, google is coming up with similar messages, but they do not apply. Any help or advice would be very appreciated.UPDATE: I notice there is a spid number in the BlkBY column in SP_WHO2 and it appears to be blocking several others, is it safe to kill the blocking spid? The spid also appears 3 times in the SPID column and is a suspended DELETE command.Regards,D.

Using Alias in Where

Posted: 26 Sep 2013 08:41 PM PDT

Good DayI would like to restrict my records to distances that are under a certain amount (I handle this later with coding) but the problem is setting the limit in my SQL. I keep getting "Invalid Column Name" error for Distance.This is my original code:[code="sql"]Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10AND Distance < 20000 AND MAct='Full' Order By Distance ASC[/code]I also tried:[code="sql"]Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10AND Cast((SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) as int) < 20000 AND MAct='Full' Order By Distance ASC[/code]And I tried:[code="sql"]Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10AND (SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) < 20000 AND MAct='Full' Order By Distance ASC[/code]Thank youGreatness... Live It!!

query issue

Posted: 27 Sep 2013 02:44 AM PDT

HiI have written a query and the output for the query isRun Date Fiscal Year Posting Period Port Fuel Oil Price Gas Oil Price20061031 2006 10 FOS NULL 578.520061031 2006 10 FOS 275 NULL20061031 2006 10 FUJ NULL 592.520061031 2006 10 FUJ 276 NULL20061031 2006 10 GEN 273 NULL20061031 2006 10 GEN NULL 568.520061031 2006 10 HOU 262 NULL20061031 2006 10 HOU NULL 537.520061031 2006 10 NYK 306 NULL20061031 2006 10 ROT NULL 472.5Query is select top 10 CONVERT( varchar(28),observation_dt,112) AS [Run Date],cast(year(applicable_dt)as varchar(28))as [Fiscal Year],cast(month(applicable_dt)as varchar(28)) as [Posting Period],cast(price_reference_cd as varchar(28)) as Port, (case r.price_type_cd when'FUEL OIL'then cast(quote_val as varchar(28)) END)as [Fuel Oil Price],(case r.price_type_cd when'GAS OIL'then cast(quote_val as varchar(28)) END)as [Gas Oil Price]from OSS_MARKET_PRICE_DAILY o inner join rd_market_price ron o.market_price_seq=r.market_price_seqwhere r.price_type_cd in ('FUEL OIL','GAS OIL') group by observation_dt,applicable_dt,price_reference_cd,quote_val,price_type_cd order by 1,2,3,4I want to get the values as Run Date Fiscal Year Posting Period Port Fuel Oil Price Gas Oil Price20130605 2013 9 HOU 599.985 574.1920130605 2013 10 HLS 597.975 573.519920130605 2013 11 SIN 596.3 572.8520130605 2013 12 STE 594.625 572.1820130605 2014 1 TRI 592.615 597.30520130605 2014 2 YOS 590.605 597.30520130605 2014 3 FOS 590.605 597.305Can any one pls let me know what to modify to get both prices values i one lineRegardsNaveen

Considerations for location of Reporting Services databases

Posted: 20 Sep 2013 08:30 AM PDT

Hello experts,I'm working on designing a SQL Server 2008 R2 Reporting Services (SSRS) topology, and a couple of my colleagues asked to see if SSRS can have its databases installed remotely, that is, separately from the other SSRS components. It looks like this is possible, but the question was also raised as to whether we could install it on our main OLTP db server. 1. My instinct tells me that this wouldn't be advisable for performance reasons, but is this feasible depending on the expected load for the SSRS installation?2. Also, does it cost more in licenses to have SSRS host its own databases, or is the cost of an SSRS database instance for the ReportServer and ReportServerTempDB databases included in the license for the main OLTP db server that doesn't have SSRS on it? For example, according to this page, scaling out SSRS seems to increase the cost dramatically.[url=http://www.networkworld.com/community/node/43349]http://www.networkworld.com/community/node/43349[/url]3. That same article also suggests that it is best to separate the web server for SSRS as well, meaning at least one more server for that component. Is this really the necessary best practice across the board, or can it depend on the size of the expected user base of SSRS users (in our case, something like 20-30 users)?Thanks for any help - I am in the middle of reading up on the related documents myself (for example here [url=technet.microsoft.com/en-us/library/ms157293(v=sql.105).aspx]technet.microsoft.com/en-us/library/ms157293(v=sql.105).aspx[/url] and here [url=http://technet.microsoft.com/en-us/library/cc966418.aspx]http://technet.microsoft.com/en-us/library/cc966418.aspx[/url]), but thought I would put this question out there in case someone happens to provide information while I'm researching.Thanks again.- webrunner

MSDTC config in cluster..

Posted: 18 Sep 2013 09:02 PM PDT

Hi All,Windows server 2008 r2I have to install sql 2008 r2 cluster for i need a help for msdtc config?Steps that i have followed:1)MSDTC configuration:i have configured msdtc instance in seprate group.2)install sql clusternow my dout is do we need to map the sql instance with MSDTC instance or not.?Please reply its urgent...

Restore backup error

Posted: 26 Sep 2013 09:57 PM PDT

Hi All,How are you doing with error messages like this one:Msg 3242, Level 16, State 2, Line 1The file on device 'D:\Backups\database.bak' is not a valid Microsoft Tape Format backup set.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.I got a backup from a client but cannot restore it.I tried with CONTINUE_AFTER_ERROR as well, but same.Actually [code="sql"]RESTORE VERIFYONLY FROM DISK = N'D:\Backups\database.bak'[/code] gives out the same error.

rename MSDTC

Posted: 26 Sep 2013 10:03 PM PDT

Hi All,How to rename MSDTC in cluster 2008?Thanks

Importing from a improperly formatted excel file

Posted: 25 Sep 2013 06:39 AM PDT

Any guidance would be appreciated. Despite my protestations I have been tasked to import from an excel file. The file is not what I would call a proper data file. The data will start in cell D10 and end in cell Kx where x is the number of data rows in the file.What are my options here. My first thought would be to use SSIS and dump say cell A1 through K10000 ( assuming I would never have more than 10000 rows) into a staging table and parse the data out.Anyone been down this road...?
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog