Friday, September 27, 2013

[how to] Optimizing join between big tables

[how to] Optimizing join between big tables

Optimizing join between big tables

Posted: 27 Sep 2013 05:26 PM PDT

I have a server on my pc and I need information across several large tables (~ 20MM each). I installed WAMP on my windows 7 and use mysql for this.

No idea how I can optimize the configuration of mysql to get faster queries. Lately it takes hours to do a join between two tables of approximately 22MM records.

  • Only 1 user (me) use this server.
  • My tables are with indices and ~20 columns.
  • Processor: i7
  • RAM: 12gb
  • HD: common
  • MYSQL 5.6.12

How could i optimize them?

What are concepts need to Learn for MSSQL DBA? [on hold]

Posted: 27 Sep 2013 02:00 PM PDT

I am MSSQL DBA. I have gone through basic concepts ,now i am decided to lead my career in MSSQL DBA. Please let me know what are the concepts i should learn for successful SQL DBA. Please help me on this.

MySQL possibility to create global routines (stored procedures and/or functions)

Posted: 27 Sep 2013 01:32 PM PDT

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database.

When I tried to create a routine from console (without prior issuing use dbname) I am getting an error:

ERROR 1046 (3D000): No database selected

We have tons of identical databases (data is different) and the goal is to create some triggers for some tablenames. But we want to run only one routine so we don't have to create those routines for every database (since they are identical, routines would work the same for each database).

Multiple system.replset collections in local

Posted: 27 Sep 2013 01:03 PM PDT

I have a replicaset that's showing multiple system.replset collection in the local db.

replset1:PRIMARY> show collections;  me  oplog.rs  replset.minvalid  slaves  system.indexes  system.profile  system.replset  system.replset

Running db.system.replset.find() returns the documents from both with out error.

They are different versions, one showing the current membership, another an older setup. I haven't seen this before. Is this expected?

I'm not really seeing any problems from the mongo or application side, this just seems strange. The duplicates are present on the primary and secondaries.

Should .remove({"version":}) be safe?

MySQL query optimization with aggregate ORDER BY

Posted: 27 Sep 2013 01:01 PM PDT

I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items."

The schema is as follows: 

CREATE TABLE `t1` (      `imps` bigint(20) DEFAULT NULL,      `n_id` int(11) NOT NULL,      `b_id` bigint(20) NOT NULL,      `date` date NOT NULL,      `m1` bigint(20) NOT NULL,      `m2` bigint(20) NOT NULL,      `is_b` tinyint(1) NOT NULL,      `i_id` varchar(128) NOT NULL,      KEY `idx1` (`b_id`,`date`,`i_id`,`n_id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

(This table is split into 256 partitions by HASH(b_id), in case that matters)

A representative query: 

SELECT i_id, n_id, SUM(m1), SUM(m2)  FROM t1  WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'  GROUP BY i_id, n_id  ORDER BY (SUM(m1) + SUM(m2)) DESC  LIMIT 10 OFFSET 0

And the EXPLAIN output: 

+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+  | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                                        |  +----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+  |  1 | SIMPLE      | t1    | ref  | idx1          | idx1 | 8       | const | 1395772 | Using where; Using temporary; Using filesort |  +----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+

On a table with ~110M records these queries take > 10s to execute. I believe the SUM function in the ORDER BY clause forces the entire resultset satisfied by the WHERE clause to be written to disk, hence the "Using temporary; Using filesort" messages.

Do I have any recourse in improving the performance of these queries short of redesigning the schema and application?

Best practices for backing up a MySQL DB

Posted: 27 Sep 2013 12:27 PM PDT

I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience with MySQL DB's. Any best practices for using 'mysqldump' or any other DB backup tools?

I'll probably cron job the schedule so that it's done nightly and then backup the files with my backup system.

Thanks.

Simple table normalisation

Posted: 27 Sep 2013 05:30 PM PDT

In raw form, I have data with 2 columns: skills in welsh and stats for that skill. For storing these information in a database, would it be best to combine the stats with the categories, or split them up as below?

WELSH_SKILLS_TABLE  ----------------------------------------------------  |  SKILL_ID   |                SKILL  ----------------------------------------------------  |      0      |            CAN'T SPEAK  ----------------------------------------------------      WELSH_STATS_TABLE  ----------------------------------------------------  |  SKILL_FK   |                COUNT  ----------------------------------------------------  |      0      |                 235  ----------------------------------------------------

Instinctively, the laid out method seems to be correct, but for such a simple task, also seems like overkill?

Thanks!

How can I verify that MySQL's slave_compressed_protocol is working

Posted: 27 Sep 2013 12:39 PM PDT

I've set slave_compressed_protocol=1 on both my master and slave.

Using SHOW VARIABLES, I can see that it's set.

How can I check that it's actually working?

Production (20GB+) Database running slowly and timing out

Posted: 27 Sep 2013 01:19 PM PDT

I know the title thread is a bit ambiguous (and my first post here!) but I am having a very severe production database problem. I am not a DBA, though I know how to work with queries and SQL Manager. The database is constantly under load, as there's a constant input and output. The problems started about a week ago and I've done everything I know to get it working... from Rebuilding Indexes to Updating Statistics to Shrinking DB. As an example of the recent problem I used to run a daily backup at 5PM and it took 20-25 minutes, now its taking over an hour. Some days the thing will work just fine, for example yesterday we didn't have an issue and today its all over the place. Its a 20+GB Database running in a VM with Windwos Server 2008R2, dual Xeon @ 2.4GHZ and 12GB of RAM plus a partition for OS, one for DB and one for backups. As one of the measures I am taking I created a separate Log partition to move the log from the DB HardDisk to its own separate HDD. Haven't yet done it as I need to detach and take everything offline. I checked the fragmentation for the main table (5+ million entries) and it was over 99%... this was AFTER rebuilding the index.

At this point, I am just stuck. The application users use is timing out, transactions not going in or out (financial institution) sometimes. I am constantly monitoring the performance, and heck even the SQL Server Activity Monitor timed out for me a few moments ago. Any suggestions and what else should I do (aside from separating the log and DB) would be greatly appreciated.

EDIT: Server RAM Usage just in case: ram[1]

EDIT2: Code used for rebuilding Index

  ALTER INDEX ALL ON DailyTransactions  REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,            STATISTICS_NORECOMPUTE = On);

Sharing a single primary key sequence across a database?

Posted: 27 Sep 2013 07:09 PM PDT

Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)? If so, is it objectively better than using a single primary key sequence across tables.

I'm a junior software developer, not a DBA, so I am still learning many of the basics of good database design.

Edit: In case anyone is wondering, I recently read a critique of a database design by one of our company's DBAs who mentioned it was a problem that the design didn't use a single primary key across the entire database, which sounded different than what I've learned so far.

Edit2: To answer a question in the comments, this is for Oracle 11g, but I was wondering on a non-database specific level. If this question does depend upon the database, I would be interested to know why, but in such a case I would be looking for an answer specific to Oracle.

Trigger - Add to a date using a subquery

Posted: 27 Sep 2013 01:05 PM PDT

(postgresql 9.2) I'v 2 tables

Table 1 (extinguisher)

  extinguisher_id (serial)  type_designation_extinguisher_type(text)  manufacturing_date (date)  life_date(date)

Table 2 (extinguisher_designation)

  extinguihser_type(serial)  extinguisher_designation(text)  lifespan(int)

lifespan can take values like: 10 or 20 and it represents years

My objective is to do a trigger that will automatically populate table1.life_date based on the extinguisher_type using table2.

The problem is that lifespan is an int and can change. The usual way to add year to a date is using date + interval '10 year'

The problem is that I want a variable instead of the 10.

At this point i'v this:

CREATE OR REPLACE FUNCTION ext_life_span()    RETURNS trigger AS    $BODY$    DECLARE  new_date timestamptz;  manu_date char(10);  intervalo char(10);  BEGIN  IF (TG_OP = 'INSERT') THEN  intervalo := to_char((SELECT lifespan FROM extinguisher_type, extinguisher where type_designation = type_designation_extinguisher_type), '99999');  manu_date := to_char(New.manufacturing_date, 'YYYY-MM-DD');  new_date := (date 'manu_date' + integer 'intervalo' );  update extinguisher set life_date = new_date;  RETURN NULL;  ELSIF (TG_OP = 'UPDATE') THEN  IF new.manufacturing_date IS DISTINCT FROM old.manufacturing_date THEN  intervalo := to_char((SELECT lifespan FROM extinguisher_type, extinguisher where type_designation = type_designation_extinguisher_type), '99999');  manu_date := to_char(New.manufacturing_date, 'YYYY-MM-DD');  new_date := (date 'manu_date' + integer 'intervalo' );  update extinguisher set life_date = new_date;  END IF;  RETURN NULL;  END IF;  END;  $BODY$  LANGUAGE plpgsql VOLATILE  COST 100;  ALTER FUNCTION ext_life_span() OWNER TO postgres;    CREATE TRIGGER insert_lifespan  AFTER INSERT OR UPDATE OF manufacturing_date  ON "extinguisher"  FOR EACH ROW  EXECUTE PROCEDURE ext_life_span();

I'm getting an error: invalid input syntax for type date: "menu_date"

Am I doing the best approach? Is there an easier way to do it?

Backing up Oracle home directory while instances are running

Posted: 27 Sep 2013 10:12 AM PDT

Can I take a backup of Oracle home directory (with cp -r) before upgrade while the instances using the home are running or is it required to shutdown instances first?

Trace files/alert logs are in different directory structure and all data files, redo log files, control files and spfiles are in ASM.

Indexing a reference column

Posted: 27 Sep 2013 12:45 PM PDT

Say I have a reference table like languages with only 20 records. Is it worth indexing a field like language_code (e.g. en, es) that's frequently used in queries? I'm wondering if an efficient server would effectively be indexing columns like this automatically.

This is specifically for MariaDB with InnoDB engine.

How can I get a list of tables contained in a database diagram?

Posted: 27 Sep 2013 12:39 PM PDT

I need to get a list of the tables contained in a diagram in SQL Server. How can I do it using an SQL script?

I don't need a list of tables in the database, but the list of tables contained in a diagram of the database in SQL Server 2008r2/2012.

Options for Data Encryption in SQL Server 2008 R2 Standard Edition?

Posted: 27 Sep 2013 10:50 AM PDT

I'm helping a friend with setting up encryption of data on SQL Server 2008 R2 Standard edition. Upon original research I thought I could use TDE but did not realize that it was only available for Enterprise or DataCenter versions of SQL Servers. Upon further research into SQL Server 2008 R2 features I saw that it does allow for "Data encryption and key management" but I'm not sure what it means or how to implement it.

What is the most efficient and low cost method for data encryption? Should I do something through SQL Server or just use third party tools to encrypt the whole volume where DB and backups are? Also if someone can point to a way to use "Data encryption" that comes as feature in standard edition I would really appreciate. Every time I search for encryption on sql server I keep ending up on how to use TDE and in current scenario it is not feasible for the size of business to purchase Enterprise Edition.

Edit:
reason for encryption - HIPAA Compliance

What is causing Waiting for table level lock errors?

Posted: 27 Sep 2013 01:23 PM PDT

We got the database hanging twice already and trying to find a cause. 

show processlist  Waiting for global read lock | INSERT INTO {myisam_table} ...

In here the disk space was full so we thought the problem was over after giving it some more but the next day at midday it hanged again:

show processlist  Waiting for table level lock | UPDATE {myisam_table} ...

What could be causing it?

Mysql Default Engine: InnoDB.

Database has a mixture of tables with both MyISAM and InnoDB engines.

Log posted here:

http://arturito.net/2013/08/28/mysql-waiting-for-table-level-lock-errors/

Does TRIGGER improve the performance?

Posted: 27 Sep 2013 04:38 PM PDT

I understand that TRIGGERs are useful, as they take care of subsequent queries, but does they have an advantage from performance point of view too?

Replacing two or three queries as

INSERT INTO table1 ...  UPDATE table2 ...  UPDATE table3 ...

with a trigger-based query as

INSERT INTO table 1

with trigger

CREATE TRIGGER test  AFTER INSERT ON table1  FOR EACH ROW BEGIN  UPDATE table2 ... WHERE id=NEW.id;  UPDATE table3 ... WHERE id=NEW.id;  END

  1. Does we still have the same three queries with identical performance? I mean does it matter if we perform the subsequent queries or the TRIGGER does?

  2. What if we use an API (like conducting queries through PHP). The subsequent queries are internal with no need to connector/driver. Does the TRIGGER improve the performance?

Additional Information: The database is mysql 5.5 with innoDB.

SQL Server Min Server Memory

Posted: 27 Sep 2013 10:50 AM PDT

using SQL 2008 R2 with SP2 with a server with 64 GB of RAM. I have set max server memory to 58GB considering SQL Server is limited to only database engine related service only. I have a question related to setting min server memory

If I set min server memory to 48 GB does that mean

a) If windows sends low memory notification to SQL -- SQL server will trims all its pool and try to maintain memory usage up to 48 GB of memory?

b) In case of aggressive working set trimming by windows -- would it leave SQL server's memory usage to 48 GB setting a "floor" for SQL's memory usage.

c) Are there any recommendation on min server memory?

Searching the web -- internet has plenty of information for max server memory but only few place I read (Brent Ozar blog for configuration best practices) to set min server memory to 50% of total available RAM on the server.

database structure - complicated requirements

Posted: 27 Sep 2013 01:07 PM PDT

I have a project to build a website but it's complicated and I'm having trouble figuring out what the best way to build the database would be to handle these particular requirements.

The site is for a local builders and farmers (and anyone else who uses heavy equipment) to rent their machinery amongst themselves. Users should be able to sign up and list an item of equipment which is then searchable and bookable by other users of the site.

So a builder might sign-up and upload a listing for his concrete mixer. Then another user can search for concrete mixers to hire between 2 dates and place a booking for the mixer through the site.

So far so good.

Problem is that the builder should be able to set a default per-day rate but they should also be able to say that through-out the month of July, or on the last two weekends in August the mixers default daily rate is different. So basically everyday could end up having a different rate and I'm having trouble figuring out what is the most efficient way to structuring the database and how to calculate the total costs of renting for several days if there's potentially a different rate every day.

At the moment I'm imaging having to loop through a 365 sized array but that can't be right. I'm a bit new to this so I'm probably just confused.

Issue after moving the ib_logfile1 and ib_logfile0 files

Posted: 27 Sep 2013 01:03 PM PDT

I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes. So I moved these files to different location and restarted the MySQL server. It worked. Before shutting down the MySQL, server I also made innodb_fast_shutdown = 0.

But I have a doubt, after MySQL shutdown can we expect mysql is an consistent state and all the data that were in the ib_logfile0 and ib_logfile1 have been written to data files. So that I can delete these files or should I do something else also.

And if the answer is MySQL will be in consistent state than why these files contain some data even after graceful shutdown and start up with innodb_fast_shutdown = 0.

[EDIT details] giving the steps i have done

Steps i followed:

  1. SET GLOBAL innodb_fast_shutdown = 0
  2. /etc/init.d/mysqld stop
  3. rm -f /var/lib/mysql/ib_logfile0
  4. rm -f /var/lib/mysql/ib_logfile1
  5. /etc/init.d/mysqld start

Is the above step is fine? if yes then how come after the clean shutdown and start up the log files contains some data. Ideally it should be empty.

SQL Server Replication: "ALTER TABLE ALTER COLUMN" is not propagated to subscribers

Posted: 27 Sep 2013 10:23 AM PDT

We are running SQL Server 2008 R2 SP1 as publisher & distributor, and SQL Server 2005 SP3 as subscriber. The replication of schema changes is activated, and the replication has been running for years, including frequent schema changes (new column, new constraints, etc).

The following instruction was sent on the publisher:

use myDatabase  alter table Tbl_TypeLignePaye        alter column natureTypeLignePaye nvarchar(12)  go

where field natureTypeLignePaye was originally nvarchar(3) null

The query ran without errors on the main database. The result is the following:

  1. The field natureTypeLignePaye still appears as nvarchar(3) in the object browser
  2. The column properties screen indicates a nvarchar type with a length of 12
  3. The change was not propagated to the subscribers

Any idea on what is going on with this database?

Publisher: object browser window vs property window give incoherent data

field type and length

Is it possible to have extra tables in a Slave with MySQL Replication

Posted: 27 Sep 2013 03:23 PM PDT

As my title mention I have a Master and a Slave database.

Master if for operations data and my slave mainly for reporting stuff.

The issue is that I need to create extra tables on reporting that can't be on the master, but the way my replication is set (the simplest one mentioned by the official doc) at the moment, this breaks the replication system.

How could I add tables on the Slave without Master caring about it ? Is it even possible ?

Replication issue - CREATE SELECT alternative?

Posted: 27 Sep 2013 05:23 PM PDT

I've an MySQL 5.1 slave for our BI team.

They need to make some CREATE SELECT with big select queries (several million lines).

As CREATE SELECT is a DDL, if the replication attempts to update some rows in same tables than the SELECT statement, replication is blocked until the freeing of the CREATE SELECT.

Do you now a good non-blocking alternative to thoses CREATE SELECT statements?

I thought to an SELECT INTO OUTPUT FILE then LOAD DATA INFILE but they will fill out our disks as BI guys like to do... :)

Max.

How to drop a DB2 instance when the instance owner was removed

Posted: 27 Sep 2013 12:23 PM PDT

This is a real sticky situation. I was handed over a machine (running an AIX 7.1), and my first task was to re-install DB2 server on it. But someone before me had conveniently removed an instance owner account, and probably recreated it. Now, the problem is this:

1) When I try to uninstall DB2, it says the instance is active and has to be dropped first.

2) When I try to drop this instance, DB2 says there is no such instance.

I am quite new to DB2 administration. Not sure how to proceed here. Any help is appreciated

Thanks

Optimizing multi-table left joins

Posted: 27 Sep 2013 01:11 PM PDT

I have four tables which I am trying to join together.

Table event

+------------+------------------+------+-----+---------+-------+  | Field      | Type             | Null | Key | Default | Extra |  +------------+------------------+------+-----+---------+-------+  | sid        | int(10) unsigned | NO   | PRI | NULL    |       |  | cid        | int(10) unsigned | NO   | PRI | NULL    |       |  | signature  | int(10) unsigned | NO   | MUL | NULL    |       |  | timestamp  | datetime         | NO   | MUL | NULL    |       |  | is_deleted | tinyint(1)       | NO   | MUL | 0       |       |  +------------+------------------+------+-----+---------+-------+

Indexes - sid,cid,timestamp,is_deleted

Table iphdr

+----------+----------------------+------+-----+---------+-------+  | Field    | Type                 | Null | Key | Default | Extra |  +----------+----------------------+------+-----+---------+-------+  | sid      | int(10) unsigned     | NO   | PRI | NULL    |       |  | cid      | int(10) unsigned     | NO   | PRI | NULL    |       |  | ip_src   | int(10) unsigned     | NO   | MUL | NULL    |       |  | ip_dst   | int(10) unsigned     | NO   | MUL | NULL    |       |  +----------+----------------------+------+-----+---------+-------+

Indexes - sid,cid,(ip_src,ip_dst)

Table tcphdr

+-----------+----------------------+------+-----+---------+-------+  | Field     | Type                 | Null | Key | Default | Extra |  +-----------+----------------------+------+-----+---------+-------+  | sid       | int(10) unsigned     | NO   | PRI | NULL    |       |  | cid       | int(10) unsigned     | NO   | PRI | NULL    |       |  | tcp_sport | smallint(5) unsigned | NO   | MUL | NULL    |       |  | tcp_dport | smallint(5) unsigned | NO   | MUL | NULL    |       |  +-----------+----------------------+------+-----+---------+-------+

Indexes - sid,cid

Table udphdr

+-----------+----------------------+------+-----+---------+-------+  | Field     | Type                 | Null | Key | Default | Extra |  +-----------+----------------------+------+-----+---------+-------+  | sid       | int(10) unsigned     | NO   | PRI | NULL    |       |  | cid       | int(10) unsigned     | NO   | PRI | NULL    |       |  | udp_sport | smallint(5) unsigned | NO   | MUL | NULL    |       |  | udp_dport | smallint(5) unsigned | NO   | MUL | NULL    |       |  +-----------+----------------------+------+-----+---------+-------+

Indexes - sid,cid

The fields 'sid','cid' are common to all the tables and are primary keys in all. The first two tables have about 1M rows while the next two have about 600K and 200K rows respectively.

I need to find distinct IP links for not deleted events. So I write a query like

SELECT   inet_ntoa(iphdr.ip_src) AS src_ip, iphdr.ip_src AS src_ip_inet, inet_ntoa(iphdr.ip_dst) AS dst_ip, iphdr.ip_dst AS dst_ip_inet, count('*') AS total, count(DISTINCT event.signature) AS count_unique    FROM   event  INNER JOIN iphdr ON event.sid = iphdr.sid AND event.cid = iphdr.cid   LEFT OUTER JOIN tcphdr ON iphdr.sid = tcphdr.sid AND iphdr.cid = tcphdr.cid   LEFT OUTER JOIN udphdr ON iphdr.sid = udphdr.sid AND iphdr.cid = udphdr.cid   WHERE event.is_deleted = 0  GROUP BY iphdr.ip_src, iphdr.ip_dst   order by total desc   LIMIT 5

The query runs fine and returns result in about 7 seconds.

The MySQL query plan is

-+--------+---------------------------------+  | id | select_type | table  | type   | possible_keys                                  | key     | key_len | ref                               | rows   | Extra                           |  +----+-------------+--------+--------+------------------------------------------------+---------+---------+-----------------------------------+--------+---------------------------------+  |  1 | SIMPLE      | event  | ref    | PRIMARY,index_event_sid,index_event_cid,is_del | is_del  | 1       | const                             | 548283 | Using temporary; Using filesort |  |  1 | SIMPLE      | iphdr  | eq_ref | PRIMARY,index_iphdr_sid,index_iphdr_cid        | PRIMARY | 8       | test.event.sid,test.event.cid |      1 |                                 |  |  1 | SIMPLE      | tcphdr | eq_ref | PRIMARY,index_tcphdr_sid,index_tcphdr_cid      | PRIMARY | 8       | test.iphdr.sid,test.event.cid |      1 | Using index                     |  |  1 | SIMPLE      | udphdr | eq_ref | PRIMARY,index_udphdr_sid,index_udphdr_cid      | PRIMARY | 8       | test.iphdr.sid,test.event.cid |      1 | Using index                     |  +----+-------------+--------+--------+------------------------------------------------+---------+---------+-----------------------------------+--------+---------------------------------+

As can be seen the index iplink (ip_src,ip_dst) is not used.

If I drop the where clause,

SELECT   inet_ntoa(iphdr.ip_src) AS src_ip, iphdr.ip_src AS src_ip_inet, inet_ntoa(iphdr.ip_dst) AS dst_ip, iphdr.ip_dst AS dst_ip_inet, count('*') AS total, count(DISTINCT event.signature) AS count_unique    FROM   iphdr   LEFT OUTER JOIN tcphdr ON iphdr.sid = tcphdr.sid AND iphdr.cid = tcphdr.cid   LEFT OUTER JOIN udphdr ON iphdr.sid = udphdr.sid AND iphdr.cid = udphdr.cid   INNER JOIN event ON event.sid = iphdr.sid AND event.cid = iphdr.cid   GROUP BY iphdr.ip_src, iphdr.ip_dst   order by total desc   LIMIT 5

The query is 2X faster (Runs in about 3.9s) and returns correct result. Also, the query plan is altered.

+----+-------------+--------+--------+-------------------------------------------+---------+---------+-----------------------------------+---------+----------+----------------------------------------------+  | id | select_type | table  | type   | possible_keys                             | key     | key_len | ref                               | rows    | filtered | Extra                                        |  +----+-------------+--------+--------+-------------------------------------------+---------+---------+-----------------------------------+---------+----------+----------------------------------------------+  |  1 | SIMPLE      | iphdr  | index  | PRIMARY,index_iphdr_sid,index_iphdr_cid   | iplink  | 8       | NULL                              | 1065036 |   100.00 | Using index; Using temporary; Using filesort |  |  1 | SIMPLE      | tcphdr | eq_ref | PRIMARY,index_tcphdr_sid,index_tcphdr_cid | PRIMARY | 8       | test.iphdr.sid,test.iphdr.cid |       1 |   100.00 | Using index                                  |  |  1 | SIMPLE      | udphdr | eq_ref | PRIMARY,index_udphdr_sid,index_udphdr_cid | PRIMARY | 8       | test.iphdr.sid,test.iphdr.cid |       1 |   100.00 | Using index                                  |  |  1 | SIMPLE      | event  | eq_ref | PRIMARY,index_event_sid,index_event_cid   | PRIMARY | 8       | test.iphdr.sid,test.iphdr.cid |       1 |   100.00 |                                              |  +----+-------------+--------+--------+-------------------------------------------+---------+---------+-----------------------------------+---------+----------+----------------------------------------------+

But since I need to filter out deleted events, I add WHERE clause to the INNER JOIN

SELECT   inet_ntoa(iphdr.ip_src) AS src_ip, iphdr.ip_src AS src_ip_inet, inet_ntoa(iphdr.ip_dst) AS dst_ip, iphdr.ip_dst AS dst_ip_inet, count('*') AS total, count(DISTINCT event.signature) AS count_unique    FROM   event  INNER JOIN iphdr ON event.sid = iphdr.sid AND event.cid = iphdr.cid and event.is_deleted = 0  LEFT OUTER JOIN tcphdr ON iphdr.sid = tcphdr.sid AND iphdr.cid = tcphdr.cid   LEFT OUTER JOIN udphdr ON iphdr.sid = udphdr.sid AND iphdr.cid = udphdr.cid   GROUP BY iphdr.ip_src, iphdr.ip_dst   order by total desc   LIMIT 5

This query runs in about 7s and gives same query plan as query 1

I also tried to use subquery to select only those events that had is_deleted=0 but it gave me an even worse performance (9s)

My questions are -

  1. How do I optimize the first query to make use of the index on (ip_src,ip_dst) (i.e. my group by terms) without sacrificing the where clause?
  2. Why does the where clause (or join with event.is_deleted=0) lead to such a drastic resetting of query plan
  3. Can I re-write the query to make it more efficient?

Extract query fingerprints and tables from large log files using pt-query-digest

Posted: 27 Sep 2013 01:09 PM PDT

I tried to parse a large log file using pt-query-digest but it gives "Out of memory!" when I try to read the file. I have 3GB RAM, 40GB empty space on HDD, the file is 20GB. I did not found anything in the documentation about memory.

I reduce the command at:

pt-query-digest --group-by fingerprint --print --no-report /path/to/largeLogFile.log  pt-query-digest --table-access /path/to/largeLogFile.log

I have 2 questions:

  1. How can I extract the query fingerprints from a large log file generated by general_log ?

  2. How can I extract all the tables that were used ?

Edit:
Tried @DTest --sample option (with 2 and 1), but the result is the same. Just before it reaches 50% memory in htop it dies every time (see screnshoot): enter image description here

I did an strace just before it dies I get:

read(3, "phone from teams as t ri"..., 4096) = 4096  mremap(0x5b27a000, 1543499776, 1543503872, MREMAP_MAYMOVE) = -1 ENOMEM (Cannot allocate memory)  mmap2(NULL, 1543503872, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)  brk(0x64c2d000)                         = 0x8c2e000  mmap2(NULL, 1543634944, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)  mmap2(NULL, 2097152, PROT_NONE, MAP_PRIVATE|MAP_ANONYMOUS|MAP_NORESERVE, -1, 0) = 0x5b07a000  munmap(0x5b07a000, 548864)              = 0  munmap(0x5b200000, 499712)              = 0  mprotect(0x5b100000, 135168, PROT_READ|PROT_WRITE) = 0  mmap2(NULL, 1543503872, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)  write(2, "Out of memory!\n", 15Out of memory!  )        = 15  munmap(0x5b27a000, 1543499776)          = 0  ... [output removed] ...

The only option I can think so far is splitting the file "bite size" pieces (let's say 20 files of 1GB each), digest them and merge the result.

SQL Server 2012 backward compatibility for backups with 2008

Posted: 27 Sep 2013 04:28 PM PDT

I have a number of clients with SQL Server 2008 and that's what I have here on my server too. I use backup files to send databases back and forth between clients and at my office.

I have read that when you create a backup from SQL Server 2012 there is no way to restore it onto a 2008 instance. I assumed that the compatibility level would take care of this problem, but it doesn't. Therefore, I am at a loss as to how to upgrade. Other than upgrade all my clients all at once, which is impossible, I can think of no clean way to do this.

I have the need to send a database to a client as well as receive a database from a client. This is my first version upgrade on SQL Server, so I'm new to this problem. Any ideas on how to proceed?
Posted by at No comments:

[SQL Server] Display Multiple Fields in Case

[SQL Server] Display Multiple Fields in Case

Display Multiple Fields in Case

Posted: 27 Sep 2013 04:23 AM PDT

How can I display multiple fields in Case statement.If I use a "," , it gives an error...Declare @DateInput as Date = '2012-06-10' Select Case when @DateInput between '2012-06-06' and '2013-06-10'then AnniversaryStartDate,AnniversaryEndDateElse NullEnd from dbo.AnniversaryDatawhere DateInput = @DateInput Error Incorrect syntax near ','.

calculate database size

Posted: 27 Sep 2013 06:29 AM PDT

Friends,how can i calculate database size with this information:Processed 8192240 pages for database 'DBOLTP', file 'rm' on file 1. ??(8192240 *8) = 65537920 (bytes) / 1024 / 1024 = 62MB?Thanks...

Jobs to do after restoring 2000 db to 2008 R2

Posted: 26 Sep 2013 09:52 PM PDT

Hi - I'd appreciate a sanity check to a list of jobs I intend to do after a restore of a 2000 db to a new 2008r2 server.1. restore 2000 db using Redgate Backup to new 2008r22. change compatibility level to 20083. set appropriate database file sizes/autogrowth/max size settings4. [url=http://sqlserverplanet.com/dba/using-dbcc-updateusage]run DBCC UPDATEUSAGE[/url][url=http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html]5. run ola hallengren's 'Index and Statistics Maintenance' script for the user db's[/url][url=http://ola.hallengren.com/sql-server-integrity-check.html]6. run ola hallengren's 'Integrity check' script for the user & system db's[/url]I'm not sure if I should run the DBCC updateusage before or after the index & statistics job -I found this article [url=http://sqlserverplanet.com/dba/using-dbcc-updateusage] http://sqlserverplanet.com/dba/using-dbcc-updateusage[/url] which suggests that the statistics should be updated BEFORE running the updateusage - does it matter?any other tips would be greatly appreciated...many thanks...

Executing a SP within a SP with Params. Syntax check

Posted: 27 Sep 2013 01:29 AM PDT

Hey guys - I'm building a SP that essentially fires of a series of other SPs which load data into tables. Since it's early in the development and I test over and over and over, I'd like to limit (duh) the test results to QTYs I determine at the time instead of hundreds of thousands of records with each test. So...I've been manually updating all the SELECT/ INSERT statements to only include records X to Y.I'm trying to have this master SP prompt me for the range of records to test and then pass those same two values to other SPs needing them. Should be very easy but I'm tripping over syntax.Here's my "master" SP.[font="Courier New"]ALTER PROCEDURE [dbo].[sp_00_BigRedButton] @StartPoint int,@EndPoint intASBEGINEXEC sp_1_DumpTestDataEXEC sp_2_InsertOwnersFromOnboardEXEC sp_3_CleanUpOwnerNamesEXEC sp_4_InsertSalesPersonsEND[/font]SPs #2 and #4 are expecting these two values as well. How can I pass these params to those SPs as well. Something like...[font="Courier New"]EXEC sp_2_InsertOwnersFromOnboard(@StartPoint, @EndPoint)[/font]Right??
Posted by at No comments:

[MS SQL Server] Memory assigned to the OS

[MS SQL Server] Memory assigned to the OS

Memory assigned to the OS

Posted: 26 Sep 2013 09:46 AM PDT

Total physical RAM on the system is 288GB.Min and Max memory assigned to SQL Server is 0 and 280GB.Should I assume that 8GB is assigned to the OS? Is there a way to verify the amount of memory assigned to the OS?

Monitoring a remote SQL Server

Posted: 27 Sep 2013 01:29 AM PDT

Can dbWarden be installed on a nonproduction SQL Server and be used to monitor a production server? I installed dbWarden in our test environment and really like what dbWarden has to offer, but I was wondering if there was a way to point the same installation to monitor our production environment as well. Thank You, Rob

Cannot generate SSPI context

Posted: 26 Sep 2013 12:03 PM PDT

Friends,I want to know how can I fix it problem... "Cannot generate SSPI context". It happened when I was connected via Windows Authentication using account active directory (exclusive for engine sql server), and this account have permission Administrator in database server. If a put Local system (engine sql server - configuration manager), success via windows authentication, but using this account that I created, have a problem: Cannot generate SSPI context. someone help me?Thanks!

DBCC CHECKTABLE not reporting errors

Posted: 26 Sep 2013 07:51 PM PDT

Hi,I've recently implemented a new DBCC INTEGRITY check process running a combination of CHECKDB (for the smaller databases) and CHECKTABLE for larger - spread over a few days. During my testing of the CHECKTABLE element, I've noticed that the CHECKTABLE does not always report the integrity errors. :w00t:If I repeatably run the check, about 1 in 4 times it reports no errors at all, the other times it does - see below :-ERRORS :-Error Level State MessageText8928 16 1 Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data): Page (1:79) could not be processed. See other errors for details.8939 16 98 Table error: Object ID 2105058535, index ID 0, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data), page (1:79). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.2593 10 1 There are 911 rows in 11 pages for object "CorruptTable".8990 10 1 CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'CorruptTable' (object ID 2105058535).No ERRORS :-Error Level State MessageText2593 10 1 There are 911 rows in 11 pages for object "CorruptTable".I should add, for the test, I manually corrupted the "test" database by manually editing the MDF files on a particular value. This issue is occuring on SQL2008(SP2), on my SQL2012 instance it seems to behave itself as expected. I appreciate that this could because this is a forced corruption, but surely a currupt database is still a corrupt database by whatever means.Has anyone else seen this behavour?Thanks.
Posted by at No comments:

[Articles] Dating Your Employer

[Articles] Dating Your Employer

Dating Your Employer

Posted: 26 Sep 2013 11:00 PM PDT

Looking for a new job? Want to pick the best one? Your employer probably feels the same way. Andy Warren talks about how your interview is more like dating than you think.
Posted by at No comments:

[SQL 2012] Issue with AlwaysOn High Availability

[SQL 2012] Issue with AlwaysOn High Availability

Issue with AlwaysOn High Availability

Posted: 27 Sep 2013 12:36 AM PDT

Friends,We are facing an issue with our DBs. We have configured AHA for couple of SQL Server Instances. The first instance has a AHA in which we have a DB from second Server Instance as Secondary. It seems that there was a failover last night and both the groups in the first server instance have become Secondary and both the groups in second instance have become Primary. Due to this the DBs have become Read Only.Please let us know the steps to resolve this issue.

SQL Server Enterprise Edition: Core-based Licensing (64-bit)

Posted: 26 Sep 2013 07:52 AM PDT

Hi guys,I'm assuming the answer is NO, but I thought I would ask anyway...Some consultants came in and installed SharePoint on SQL Server 2012 Enterprise Edition: Core-based Licensing (64-bit) before I started working at my current company. I need to change this to Enterprise Edition. I don't need the Core Based Licensing. Does anyone know if there is a way of doing this besides uninstall/reinstall?

sql-2102

Posted: 27 Sep 2013 02:04 AM PDT

Hiam installing 2012 - SQLEXPRWT_64_ENU_2 as components are not installed and am stuck:I already have MSSQLSERVER MSSQL11.MSSQLSERVER (EDITION- express)I also have MSSQLSERVER1 MSSQL11.MSSQLSERVER1 (EDITION- express)I also have MSSQL10.SQLEXPRESS (EDITION- express)I want to keep the instance name as MSSQLSERVER. should I perform new installation or add features to an existing instance of sql server 2012.Thanks

sql 2012- 2008

Posted: 27 Sep 2013 01:41 AM PDT

I have sql server 2008 - which am trying to uninstall, the only tools I have - can see is import export data 64 bit and configuration tools. I cannot uninstall through control panel as they are not shown.please help.

Restricting Tables from SysAdmin

Posted: 26 Sep 2013 11:14 PM PDT

I have a database which, among other items, has sensitive, Executive payroll tables. As the Developer, I have been asked to restrict these tables so only selected operators can view/update them. Although as the Developer, I am allowed access, the System Administrator should not be. I am not able to use traditional methods of limiting access by user group, because the SysAdmin can simply add himself to whatever group has access. I already have password protection within the application itself, so when an operator goes into Payroll, Executive Payroll isn't an option. However, if an authorized user uses Excel, Crystal Reports, etc. to connect directly to the database, then the security built into the application is bypassed. Here are the ways I'm considering so far, although I don't know if any of them will work.1. Encrypt or password protect selected tables.2. Encrypt selected columns of the selected tables when the new rows are added or existing rows are modified. Decrypt the columns when the rows are accessed.3. Create a separate database on the executive payroll operator's local drive (there is only one executive payroll operator). When the application opens, if would either copy the local tables to the common database or link to them. When the application closes it would either copy the tables back to the local drive and remove them or remove the link to the tables on the local drive.Any other ideas are appreciated.

Logshipping between 2008 and 2012

Posted: 27 Sep 2013 01:01 AM PDT

Greetings Earthlings, I'm setting up logshipping between primary 2008 and destination 2012 servers. It is going well as I am moving from database to database although as I am getting to the larger databases I am seeing potential issue coming up. Here is the workflow to establish log shipping that I have figured out. If you have an improvement to the process then I would welcome to hear it: Backup DatabaseA on SQL2008Copy the backup to SQL2012Restore the database with recovery (I can not restore with standby because it needs to go through an upgrade to 2012)Backup the now upgraded database to diskRestore the database with Standby/Read OnlyTurn on log shipping jobs (TL backup on 2008, Copy on 2012, TL Apply on 2012)All works fine from then on. Temporarily, we do not have enough space on the destination for a backup of the larges logshipped database. Therefore I have tried to utilize restoring to 2012 from the \\sql2008\backupdrive. That worked fine. Backing the upgraded 2012 database to the \\sql2008\backupdrive has failed me. Most likely due to network's unability to maintain quality connection during the 2Terrabyte backup.

SQL 2012 Memory Clerks

Posted: 27 Sep 2013 12:00 AM PDT

Hi AllPrior to SQL 2012, when using the sys.dm_os_memory_clerks, the MEMORYCLERK_SQLBUFFERPOOL clerk always showed zero, this was because single_pages_kb refers to the amount of single pages the clerk is pulling from the buffer pool, so the buffer pool won't be reading single pages from itself, hence the 0kb. Has this changed in SQL 2012? I am seeing huge amounts for this clerk now which I am assuming is the database data/index pages. Thanks

Low memory usage and low PLE on 23GB virtual server

Posted: 26 Mar 2013 10:24 PM PDT

I am trying to help a customer with a performance problem on a SQL server that runs several different OLTP systems. The system is continuously handling hundreds of batches per second. CPU load is very low, the disk is 100% busy almost all the time.Users are complaining about longer response times than expected. This is a brand new server, so we have no previous baseline.This is a virtual server using Hyper-V, the physical server is only used for this virtual server.16 logical CPU:s28GB memory23GB allocated to SQL server using minimum and maximum server memorySQL Server 2012, Standard editionLock Pages in memory is NOT enabledWhen looking at memory usage on the machine, there does not seem to be any external memory pressure - there is no paging and there seems to always be physical memory available.When trying to diagnose the problem I have found something that I think might be the cause of the heavy disk read activity.I watch the following perfmon counters:SQL Server Memory Manager:Free Memory (MB)SQL Server Memory Manager:Database Cache Memory (MB)SQL Server Buffer Manager:Page Life ExpectancyWhen the server is loaded I would expect Free memory to be very low, Database Cache memory to be close to the maximum, and Page Life expectancy to be high.What I am seeing is instead that Free memory is constantly about 16 GB, Database Cache memory varies between 200 MB and 2 GB. Page Life expectancy repeatedly drops to 0, climbs to 100, and drops to 0 again.So, there is lots of free memory available, but it is not getting used!I have used SQL Profiler to look for any SQL commands that reads has a large amount of reads, but I ave not found anything significant. There are simply a very large number of small requests.Do you have any ideas about what could be causing this behavior?Any ideas about how to continue trying to find the cause of the problem?/SG
Posted by at No comments:

[T-SQL] Duplicate key was ignored warning returned even when no duplicates are found

[T-SQL] Duplicate key was ignored warning returned even when no duplicates are found

Duplicate key was ignored warning returned even when no duplicates are found

Posted: 26 Sep 2013 08:39 PM PDT

Hi I am having problems with the "Duplicate key was ignored" warning message. The problem is that the message seems to happen randomly and cannot be reproduced. If i take the same set of data and run the stored procedure that causes the problem i don't get the warning message a second or subsequent time. Also all the SELECT statements have criteria set to remove duplicates before they are inserted into the tables.[b]Background to the problem:[/b]I have a data feed that pulls data from a DB2 database to a SQL Server 2008 staging table as a flattened set of records. A stored procedure in SQL Server is run to load the data into the destination tables. The data feed is run hourly for new and updated records in DB2 Monday-Friday 09:00-17:00 and then there is a midnight run of all the records going back for the last 12 months.The data feed was originally sent from DB2 as a CSV file and pulled into SQL Server using SSIS but is now an Informatica workflow that pulls the data directly from DB2. It is the Informatica workflow that is returning the "duplicate key was ignored" warning message and this stops the workflow. The workflow is restarted and the data is always loaded the second time without the warning message. The warning does not happen every time the workflow is run - it can run for a number of days with no warnings and then one will come throughI can see in Profiler that it is SQL Server that returns the Duplicate key was ignored warning message so it is not an issue with Informatica. I cannot reproduce the problem to get to the root cause of the issue. I would expect that if i run the same set of data through the stored procedure i would get the warning message every time, but this is not the case. Even when i step through the stored procedure i do not get the message. As the midnight data feed returns the records from the last 12 months, so by definition would include duplicates, the warning message only appears randomly and is not consistent.Is this a bug in SQL Server or does anyone have a suggestion of where else to look?

ORDER BY clause based on values IN ()

Posted: 26 Sep 2013 10:26 AM PDT

Hi all,So my client want to run a SQL query to list data along the lines of SELECT field1, field2, field3 FROM table WHERE field1 IN (83944, 83955, 83954, 83951,83947, 83946, 83953)The output needs to be in the same order that the numbers are in the IN clause, in other words 83944, field2, field383955,field2, field383954, field2, field383951, field2, field383947, field2, field383946, field2, field383953, field2, field3There is no other ordering based on another field possible. The client is manually typing in those numbers into the IN in the specific order he wants the output.Is there some way that SQL knows the order of each element in the IN brackets?I know I could write some fairly simple T-SQL with a temp table and an identity field. Then do an INSERT INTO #TempTable.SELECT * FROM #TempTable ORDER BY IdentityFieldBut looking for another way maybe?

Data not loaded in email?

Posted: 26 Sep 2013 08:00 PM PDT

HiResult set is not loaded in Email body, what could be worng in this script?[code="sql"]declare @results varchar(max)select MACHINE, COUNT(*) as Instance into #tableAfrom LIMSPROD.dbo.CM_INSTANCE group by MACHINESelect COUNT(*)/4 as InstanceA into #tableBfrom LIMSPROD.dbo.CM_INSTANCESelect A.MACHINE, A.Instance, B.InstanceA, (B.InstanceA-A.Instance) as Diff into #resultsfrom #tableA A, #tableB Bwhere (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3-- number of records to not be zeroIf ((select count(*) from #results) <> 0)BeginSET @Results = 'select * from #results'EXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',@recipients='ananda.murugesan@xyz.com',@subject='Alert!-Verify Instance',@query=@ResultsEnddrop table #tableAdrop table #tableBdrop table #results[/code]Error msg in job historyNT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.Thanksananda

Query that returns only columns with data

Posted: 26 Sep 2013 08:07 AM PDT

I have a table with a large amount columns. Instead of trying to manually go thru the table and determine which columns have data. Is there a way to make a select that will return only the columns with data in them (omitting any columns w/out data). Without data, I am referring to all nulls or empty strings, etc.
Posted by at No comments:

[SQL Server 2008 issues] data copy

[SQL Server 2008 issues] data copy

data copy

Posted: 26 Sep 2013 07:17 PM PDT

I have a DEV database i.e DEVDBI also have a TEST database i.e TESTDBboth are SQL SERVER 2008I want to import data from TESTDB to DEVDB.Is it possible through SQL Server Management Studio Wizard ? Is it faster than other approach ?I see a Import/Export on GUI of SQL Server Management Studio Wizard. Which datasource to use to copy data ?

Is it possible to install adventureworks 2012 database inside SQL server 2008 R2?

Posted: 26 Sep 2013 06:07 PM PDT

I tried doing that and got an incompatibility error. I was wondering if there is a workaround or fix for that or if there is a 2012 DB for SQL 2008.The error message is -TITLE: Microsoft SQL Server Management StudioAttach database failed for Server 'YourComputer'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)The database 'AdventureWorks2012' cannot be opened because it is version 706. This server supports version 661 and earlier. A downgrade path is not supported. Could not open new database 'AdventureWorks2012'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=948&LinkId=20476

replacing numbers with strings using PATINDEX

Posted: 26 Sep 2013 06:54 PM PDT

Hi I am migrating data from a legacy system to a new system. I am trying to replace legacy client id with the new client ids.[code="sql"] create table sampleclient (clientid int, clientcode varchar(10)) INSERT sampleclient VALUES (1, 'ABCD'), (2, 'EFGH'), (3, '3RETS'), (4, 'T6UY');[/code]I am trying to create a function where i can pass in a string with the old client id and return the new clientcode.[code="sql"]select dbo.ReplaceClientIDwithClientcode('ActiveMaster.Fields("ClientID").Value = 1')[/code]I am expecting the output to be [quote]ActiveMaster.Fields("ClientID").Value = 'ABCD'[/quote]Some of the input strings are bit complex.[quote]ActiveMaster.Fields("ClientID").Value <> 356 and ActiveMaster.Fields("ClientID").Value <> 481 and ActiveMaster.Fields("ClientID").Value <> 17 and ActiveMaster.Fields("ClientID").Value <> 1775[/quote]Here is the code i have so far.[code="sql"] DECLARE @clientid varchar(100), @clientcode varchar(100), @string varchar(3000), @start int, @end int, @len int SET @string = 'ActiveMaster.Fields("ClientID").Value <> 356 and ActiveMaster.Fields("ClientID").Value <> 481 and ActiveMaster.Fields("ClientID").Value <> 17 and ActiveMaster.Fields("ClientID").Value <> 1775'while PATINDEX('%[0-9]%',@string) > 0begin set @len = len(@string) set @start = PATINDEX('%[0-9]%',@string) --print 'start of first number ' + cast (@start as varchar(1000)) --print substring(@string, @start, @len) set @end = PATINDEX('%[^0-9]%',substring(@string, @start, @len))-1 --print @end if @end = -1 begin set @clientid = substring(@string, @start, @len) end else begin set @clientid = substring(@string, @start, @end) end print @clientid set @clientcode = (select clientcode from clients where ClientID = @clientid) print @clientcode set @string = REPLACE(@string, @clientid ,''''+ @clientcode + '''') --set @loopbreaker = @loopbreaker +1 --if @loopbreaker = 10 -- breakend print @string [/code]The problem i am facing is that the client code which replaces the client id also contains numbers. My loop finds the number again and chops up the client code.Any help is greatly appreciated.

Find Locked objects with high waitstats

Posted: 26 Sep 2013 06:40 PM PDT

Hi,I have a SQL JOB that takes almost 8 hours to complete. I could find that when this runs there are a lot of waits. Screenshot is attached.I would like to know on which objects these locks and latches are occurring and if possible the SQL statement involved. These locks and latches are not being kept for a long duration, so it is hard to track from sysprocesses.Thanks in Advance !

Logshipping status report Throgh Mail

Posted: 28 Feb 2012 06:04 PM PST

i'm writing below this code..exec msdb..sp_send_dbmail@profile_name = 'local',@recipients='chintu@www.com', @subject ='Log Shipping Job succeeded', @QUERY = 'exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N'I want Log shipping Status Through Mail.please Suggest Me.....because last back up transaction in the day and last restoration trn file in the day...so may be above script right..please help me.

Need Urgent Help with Capacity Report Script

Posted: 16 Apr 2011 07:52 AM PDT

I really need help with SQL 2008 Script on generating the Capacity Report with following columns;- Execution date as getdate()- ServerName-Database Name-DB-Size in MB-Log-Size in MB-DB-Used space in Mb-Log-Used space in Mb-DB- Available free space-Log- Available free Space- Quartery growth in MB-1 year Forcast growth in GBI need the store procedure that creates the table if not exists and load the above information from systems table so that I can call that store procedure via sql agent job on daily basis. Please some help me with the script-Thanks in advance!

Need to combine multiple sql queries into one to produce a single outcome

Posted: 26 Sep 2013 05:27 PM PDT

Greetings, I have several queries that all produce a "yes" or "no" outcome. I need to consolidate these queries in a way that will produce a count for each "yes" and produce a result like 2 out of 3. So if 4/4 is produced it would say challenge, 3/4 benchmark, 2/4 strategic, 1/4 & 0/4 intensive. What's the best way to do this? Is it even possible? Greatly appreciate any help on this.Here are the queries:1.)[code="plain"]SELECT CASE WHEN HISTORY >= 6 AND ELA >= 6 AND MATH >= 6 AND SCIENCE >= 6 AND FL >= 6 AND VA >= 6 AND Prep >= 6 THEN 'Yes' ELSE 'No' END AS Yes_No FROM ( SELECT STU.ID, COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY, COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA, COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH, COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE, COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL, COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA, COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep FROM dbo.CRS INNER JOIN dbo.HIS ON CRS.CN = HIS.CN INNER JOIN dbo.STU ON HIS.PID = STU.ID WHERE STU.ID = @ID) AS derived[/code]2.)[code="plain"]WITH cteSource(CN, U1)AS ( SELECT r.CN, r.U1 FROM dbo.SSS AS s INNER JOIN dbo.STU AS t ON t.SN = s.SN INNER JOIN dbo.CRS AS r ON r.CN = s.CN WHERE t.ID = @ID UNION ALL SELECT r.CN, r.U1 FROM dbo.HIS AS i INNER JOIN dbo.CRS AS r ON r.CN = i.CN WHERE i.PID = @ID)SELECT CASE WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes' ELSE 'No' END AS [On Target?]FROM cteSource AS sPIVOT ( COUNT(s.CN) FOR s.U1 IN ([A], [B], [C], [D], [E], [F], [G]) ) AS p;[/code]3.)[code="plain"]SELECT MIN(CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' ELSE 'No' END) AS PassFROM TST INNER JOIN CTL ON TST.PT = CTL.PTWHERE (CTL.NM LIKE '%- total') AND (TST.PID = @ID) AND (TST.ID = 'CAHSEE')[/code]4.)[code="plain"]SELECT CASE WHEN [Total Absences] < @Absences THEN 'Yes' ELSE 'No' END AS [On Target?]FROM (SELECT SUM(CASE WHEN Val = 'A' THEN 1 ELSE 0 END) AS [Total Absences] FROM (SELECT ATT.* FROM ATT INNER JOIN STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN WHERE STU.ID = @ID) t UNPIVOT (Val FOR Cat IN ([A1], [A2], [A3], [A4], [A5], [A6])) u) AS derived[/code]

SSIS source file with time in the name

Posted: 26 Sep 2013 06:18 AM PDT

Hi everyone.I have been tasked with building some SSIS packages to load data from a csv into the database each day. There are four file sources. Three of them are very simple because the file name is source_date.csv. The file name is predictable so creating an expression that get the current day's file is easy. One of the files is source_date_time. Because the time stamp is always a few seconds different, I don't know how to create an expression to get the file by name and load it.What do people do when they need to have their package go out and grab the day's file if there is a time stamp in the name leading to an unpredictable file name?

SSIS - ForEachLoop Container performing task twice for the last file

Posted: 26 Sep 2013 07:27 AM PDT

Okay, so I can't figure this out and it's driving me nuts. It's minor thing but I don't have any answer. Maybe some SSIS expert can shed some light.[b]Problem[/b]I have a Foreach Loop container with file system task. File system task renames all the files in the folder; it basically adds date and time in front of the file name. For the last file in the folder, it adds date and time twice. See attached Image. [b]Here is how I have it setup[/b]1. ForEach Loop Container: Under collection: Enumerator is "Foreach File Enumerator". Then my folder is populated. "Retrieve file name" is setup to be "Name Only". Then I assigned it to a variable. See attached image.2. Setup a variable for the file location/folder. 3. Setup a variable to create date and time. Here is the code: [code="vb"](DT_STR, 4 , 1252)DATEPART( "year" , GETDATE() ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "month" , GETDATE() ) , 2 ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "day" , GETDATE() ) , 2 )+(DT_STR,2,1252)DATEPART("hh",GETDATE())+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+(DT_STR,2,1252)DATEPART("ss",GETDATE())[/code]4. Set up a variable to create original file name with full path by using the path stated in step #2+#1.5. Set up a variable to create new file name Using steps #2+#3+#1. Here is the code: [code="vb"]@[User::FROIOriginalLocation] + @[User::FileNameDate]+ @[User::FileNamesForNameChange]+".txt"[/code]What do you guys think is going on?

Data loading performance

Posted: 26 Sep 2013 12:41 PM PDT

HelloI have to transfer 300gb's of data from a table in one database to a table in another database on the same server. I've done some research into data loading and testing and found the quickest and most efficient way to do this was set the recovery to BULK_LOGGED, turn on trace flag 610 and lock the target table. I have also ordered the data set on the primary key of the target table as well.The current transfer rate that I'm getting is around 1gb per hour.My question is would I get a quicker transfer rate if I was to unload the data to a file using BCP or bulk insert and then load it back into the target database and table?

Question about select count(*)

Posted: 26 Sep 2013 02:57 PM PDT

Hi,I got a question about select (*),when someone is executing a transaction that inserts huge number of records, and before it has been committed, will select count(*) get the keep changed record numbers even if (nolock) hint is on?Thanks,V

can you delete the contents of the updatecache folder

Posted: 26 Sep 2013 03:39 PM PDT

this directory is over 2gb\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cachecan you delete the contents safely?

Newbie needs help with a query!!

Posted: 26 Sep 2013 06:01 AM PDT

CREATE TABLE [dbo].[Schedule]( [id] [int] IDENTITY(1,1) NOT NULL, [weekdayid] [int] NULL, [starttime] [varchar](10) NULL, [endtime] [varchar](10) NULL) GOINSERT INTO [dbo].[Schedule]VALUES(1,'9:00am','10:00am')INSERT INTO [dbo].[Schedule]VALUES(1,'11:00am','12:00pm')INSERT INTO [dbo].[Schedule]VALUES(1,'2:00pm','4:00pm')INSERT INTO [dbo].[Schedule]VALUES(1,'6:00pm','8:00pm')id weekdayid starttime endtime----------- ----------- ---------- ----------1 1 9:00am 10:00am2 1 11:00am 12:00pm3 1 2:00pm 4:00pm4 1 6:00pm 8:00pmHow to validate parameters passed as @startime.@endtime from SP should not fall outside the timeframe for the same day.

Need advice for best option

Posted: 26 Sep 2013 03:48 AM PDT

I have a 600GB database that I need to copy up to date information to. I must have it in a ready only state while we migrate information from it. I cannot replicate because all the tables do not replicate and I need all of them. Log shipping was a bust, thinking of mirroring but I need it in a read only state. I read that I can put it in standby mode for read only but will I be able to access it for reading purposes? I get conflicting answers out on the web and want you guys to chime in.Thanks in advance!

How to find where sp_spaceused is being executed from?

Posted: 15 Mar 2013 03:30 AM PDT

I inherited an environment where a couple of times a day, we start getting a single spid blocking other spids which causes errors in the application. In researching it using sp_who2, I see the command as DBCC, the program as OSQL-32 and the execution as sp_spaceused @updateusage = 'TRUE'. This runs for 15 minutes or so and then the errors go away because the spid is no longer blocking.I don't know where this is being executed from? I have researched the jobs that are scheduled in Windows during that time and don't see it. I have looked at SQL schedules and don't see it. Is there an easy way to track down where this is coming from?

WestCoast - EastCoast Speed

Posted: 26 Sep 2013 05:32 AM PDT

Our database is physically located on the East Coast, we have a COTS product were our users uses it to read, open modify records in the database as well as run reports. Our West Coast users complains how slow the application takes to respond. What can be done to help easy their pain when using the COTS application. I guess because they are on the west coast it's going to be slow and I'm talking 20-40 seconds slow.

Jobs that run during specific duration

Posted: 26 Sep 2013 02:40 AM PDT

Hi,Do you have any handy script where I can get the list of jobs that ran during specified time period on a particular day.For example, I need the job details that were running on 25th Sep 2013 between 2AM to 3AM. It is not necessary that job has to run from 2AM to 3 AM. The job may get completed/failed at 2.05 or the job might have started at 2.55. All the jobs that ran any time during this time span has to be listed.Please help.

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager

Posted: 14 Dec 2012 02:27 AM PST

How to install an oracle linked server on SQL Server 2008 r2 failover cluster managerI'm reading some articles and applying them but with no result.First of all where can I get the ODBC driver free and the install, as I got the driver but does include the odbc_install.exeThanks for your help, this is driving me crazy.Thanks againAPA

Save float valur with a stored procedure adds some decimal numbers

Posted: 26 Sep 2013 02:18 AM PDT

Hi!I'm having a strange behaviour with a stored procedure that has some parameters in float format.I used:float x = (float)1.5;float y= (float)4.3;...row["x"] = x;row["y"] = y;I passed both parameters as this:cmd.Parameters.Add("@y", SqlDbType.Float).Value = (float)row["x"];cmd.Parameters.Add("@x", SqlDbType.Float).Value = (float)row["y"];And when I check in the data saved in database, it appears like this:x = 1,5y = 4,30000019073486Anyone knows why this happens?Regards

listing of months for a particular year

Posted: 26 Sep 2013 12:52 AM PDT

Dear All,Hope you are doing fine. What am I trying to do is get a stock count for each item per month for a particular year. I am trying to do a report on that.Table1: list all Purchase OrdersPoNum Date P001 2013-01-01 P002 2013-02-01 P003 2013-02-10P004 2013-03-01Table2: list items for each PoNumPoNum ItemRef QtyP001 I0001 10P001 I0002 5P002 I0003 15P003 I0003 20P004 I0003 5is it possible to have something like that?Year 2013Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec I000110 0 0 0 0 0 0 0 0 0 0 0I0025 0 0 0 0 0 0 0 0 0 0 0I0030 35 5 0 0 0 0 0 0 0 0 0Basically, I will need each item's qty per month for a year.I came up with something. But I have the item's qty for the month the items are in only. How do I get it 0 for the other month?I was reading on creating a CTE to list the months of the year. But I am stuck.Please advise.Thanks,Ashley

TDE Restore Problem - Intermittent - anyone else experienced this?

Posted: 26 Sep 2013 01:46 AM PDT

Hi All,We have a very strange problem restoring backups which has only started since TDE was implemented on 19th September 2013. This is on SQL Server 2008 (SP3) EE, running on Windows Server 2008 Enterprise (SP2) on a paired cluster. When TDE was initially implemented, test backups and restores were successfully completed without issue. Yesterday though, a backup was taken and proved to not restore (Msg 5243, Level 16, State 6, Line 1An inconsistency was detected during an internal operation. Please contact technical support.). We then attempted to restore scheduled backups from the previous 3 nights - same problem.Early this morning the instance was failed over to the other node. Test backups were taken and successfully restored. 5 backups between 05:30 and 11:45 proved to restore successfully. A further backup was taken at 13:30..... and the problems with restore re-started ;-) This leads us to believe that restarting SQL resolved the problem.... maybe memory related?There's nothing helpful in the event logs. There are stack dumps for each attempted restore.We've contacted MS, but in the interim I was wondering if anyone out there had experienced anything similar? We are suspecting TDE (as that is the only change made recently, and backups taken prior to TDE implementation still restore perfectly). It could of course be totally co-incidental and not related to TDE at all!Any help, ideas, thoughts etc would be gratefully received.CheersCatherine

Fullscan statistic refresh produces badly performing histogram

Posted: 26 Sep 2013 01:07 AM PDT

Hello there,I have found an issue in our environment that I cannot explain.There is a statistic based off of two columns in our central table that are used in many stored procedures to JOIN back and forth to this table. There is an auto generated column statistic based off of these two columns. This table as ~47,000,000 rows.When the statistic is refreshed WITH FULLSCAN, it produces a histogram with 96 steps. Numerous stored procedures that rely on this statistic then take several seconds to complete.When the statistic is refreshed with a really low row count sample, or dropped and recreated from scratch without giving any sample rate allowing SQL to determine the sample to use, the procedures that were previously taking several seconds to run, complete in < 1 second.My question is, how can a full scan statistic refresh produce a histogram that causes performance issues over a very small sampled refresh.

Select record from group

Posted: 12 Sep 2013 01:19 AM PDT

I was assigned a difficult project. It is beyond by skill. I need expert to help me.From sample data, 1) Group by ID and COLOR2) From this group, select ID, min(DATEFROM),max(DATETO) and Color and then insert into a new table,3) For example, goup 111 and blue, select min(DATEFROM)=01/10/2012 and max(DATETO)=03/16/20124) Keep on selecting until end file.Here is sample data:ID DATEFROM DATETO COLOR111 01/10/2012 01/11/2012 BLUE111 02/02/2012 02/02/2012 BLUE111 03/15/2012 03/16/2012 BLUE111 04/05/2012 04/05/2012 GREEN111 05/25/2012 05/26/2012 GREEN111 06/06/2012 06/16/2012 GREEN111 07/17/2012 07/17/2012 BLUE111 09/08/2012 10/10/2012 BLUE222 01/10/2013 01/11/2013 RED222 02/02/2013 02/02/2013 RED222 03/15/2013 03/16/2013 RED222 04/05/2013 04/05/2013 GREEN222 05/25/2013 05/26/2013 GREEN222 06/06/2013 06/16/2013 GREEN222 07/17/2013 07/17/2013 BLUE222 09/08/2013 10/10/2013 BLUEFinally, I need select records and insert a new table like below:111 01/10/2012 03/16/2012 BLUE111 04/05/2012 06/16/2012 GREEN111 07/17/2012 10/10/2012 BLUE222 01/10/2013 03/16/2013 RED222 04/05/2013 06/16/2013 GREEN222 07/17/2013 10/10/2013 BLUE

Getting error message when trying to load XLSM file using SSIS package in SQL Server 2008 environment

Posted: 25 Sep 2013 07:42 PM PDT

Hi All,I use an SSIS package to dynamically load data from specific tabs of xlsm files into a database.The files are all structured identically, occasionally I find inconsistencies in cell formats which may cause issues when loading and usually this is resolved by modifying the cell format.The SSIS package loads over 250 files successfully but I am now experiencing an issue with one file which will not load in my server environment, however it does load successfully in my test environment.The error messages I get in the BIDs Development environment on the server environment are[i]Error: There were errors during task validation.[SSIS.Pipeline] Error: One or more component failed validation.[SSIS.Pipeline] Error: component "Excel Source" (1) failed validation and returned error code 0xC020801C.[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.[u][/u][/i] The test environment is 64 bit Windows 7 Professional with SP1, SQL Server 2008 R2The server environment is 64 bit Windows Server 2008 R2 Standard, SQL Server 2008 R2Any enlightenment appreciated,Thanks
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog